MSSQL SQL string intercepts instance code

Source: Internet
Author: User
Keywords Network programming MSSQL tutorials
Tags binary code data expression function network network programming programming

MSSQL SQL string intercepts instance code

About string interception We use the SUBSTRING function

SUBSTRING (expression, start, length) returns part of a character, binary, text, or an image expression.
Parameters
Expression
is a string, binary string, text, image, column, or an expression that contains a column. Do not use an expression that contains aggregate functions.
Start
Is an integer that specifies the starting position of the substring.
Length
Is an integer that specifies the length of the substring (the number of characters or bytes to be returned).


Left (character_expression, integer_expression) returns a specified number of characters starting from the left-hand side of the string.
Parameters
character_expression
A character or binary data expression. Character_expression can be constants, variables, or columns. Character_expression must be a data type that can be implicitly converted to varchar. Otherwise, use the CAST function to explicitly convert the character_expression.
Integer_expression
is a positive integer. If integer_expression is negative, an empty string is returned.
return type
varchar


Right (character_expression, integer_expression) returns the integer_expression character in the string that is specified from the right-hand side.
Parameters
character_expression
An expression that consists of character data. Character_expression can be constants, variables, or columns of character or binary data.
Integer_expression
Is the starting position, expressed as a positive integer. If integer_expression is a negative number, an error is returned.
return type
varchar

Look at the example below
One column has a value of

Title
Aaaa,bb,cc
Bb,ff,gg
ii

Now I want to intercept it.
AAAA BB cc
BB ff GG
ii

Which is divided into 3 fields.
According to "," because the requirement may be before the 2nd comma and after the first comma
So according to the comma to judge better!

If object_id (' tempdb ... #tb ') is not null
drop table #tb
Go
CREATE TABLE #tb (title varchar (50))
Go
Insert INTO #tb select ' AAAA,BB,CC '
UNION ALL SELECT ' Bb,ff,gg '
UNION ALL SELECT ' II '
Go


Select
Title1 = ParseName (replace (title, ', ', '. '), Len (title)-Len (replace (title, ', ', ')), +1,
Title2 = ParseName (replace (title, ', ', '. '), Len (title)-Len (replace (title, ', ', ', ')),
Title3 = ParseName (replace (title, ', ', '. '), Len (title)-Len (replace (title, ', ', ')-1),
Title4 = ParseName (replace (title, ', ', '. '), Len (title)-Len (replace (title, ', ', ')-2)
From #tb

/*
Title1 title2 Title3 title4
-------------- -------------- -------------- --------------
AAAA BB cc NULL
BB ff GG NULL
II NULL NULL NULL

(3 rows affected)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.