Technical Article

Extracting string after and before a Character/Pattern

,

Usually we see lof of codes flying around for this extraction.Most of them difficult to remember.
An easy way is to get hold of the basics.
Function used : SUBSTRING,CHARINDEX
Substring syntax : SUBSTRING(string to search, position to start, length of characters to be extracted)
CHARINDEX (character to search, string to search) returns the position of the character in the string.
If we want to extract before the character you would put the charindex as the number of characters and start position as 0 in the substring function
Usually we see lof of codes flying around for this extraction.Most of them difficult to remember.
An easy way is to get hold of the basics.

Function used : SUBSTRING,CHARINDEX

Substring syntax : SUBSTRING(string to search, position to start, length of characters to be extracted)

CHARINDEX (character to search, string to search) returns the position of the character in the string.

If we want to extract before the character you would put the charindex as the number of characters and start position as 0 in the substring function

----select characters before /

select SUBSTRING ('abcde/wxyz',0,CHARINDEX('/','abcde/wxyz'))

Similarly if you wanted to extract after the character, you would put the Character position +1 as start position and as you do not know how many characters are there after that till the end of string, you would simply put the whole length of string for safety:)

--select characters after /

select SUBSTRING('abcde/wxyz',CHARINDEX('/','abcde/wxyz')+1,LEN('abcde/wxyz')) 


Similary 

----select characters before / including /

select SUBSTRING ('abcde/wxyz',0,CHARINDEX('/','abcde/wxyz')+1)

--select characters after / including /

select SUBSTRING('abcde/wxyz',CHARINDEX('/','abcde/wxyz'),LEN('abcde/wxyz')) 


by the way if you arelooking for patterns inseatd of characters,use PATINDEX with similar logic as above.

Eg 

--select characters before '1234'

select SUBSTRING ('abcde1234wxyz',0,PATINDEX('%1234%','abcde1234wxyz'))

Rate

4.4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4.4 (5)

You rated this post out of 5. Change rating