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)