Extract characters after character '_' in string

Question

I have the following string in sql server where the length is variable

Declare @string Varchar(max) = 'abjrc_chdc_hyku_abv_ytr_DCD_HTR_TBF'

How could I select the characters after the 4th '_' in this string? (expected result: ytr_DCD_HTR_TBF)

Also how to select only the 4th part of the string (result: abv)

Any Ideas?

Thanks

score 0 · Answer 1 · answered Dec 03 '18 at 17:16

0

If you know exactly how many characters are in the string at any time, then you can use SUBSTRING, so you can call the first string like this

Declare @string Varchar(max) = 'abjrc_chdc_hyku_abv_ytr_DCD_HTR_TBF';
Declare @last4 Varchar(max) = SELECT SUBSTRING(@string, 21, 35);
Declare @before4 Varchar(max) = SELECT SUBSTRING(@string, 17, 19);

If y ou really need to split the string for each '_' then you can use this as well hope i helped.

answered Dec 03 '18 at 17:16

Alei Ruiz

63
5

The substring work if the @string does not change, however I'm looking a Dynamic query to select the required characters. The length between the '_' is varaible – Comando_sur Dec 03 '18 at 17:22
Look up at the link i left in the comment, i think it migth help you – Alei Ruiz Dec 03 '18 at 17:25

score 0 · Answer 2 · answered Dec 03 '18 at 17:47

If you will do it for multiple line creating function would be better. I have prepared sample for your two question :

Declare @string Varchar(max) = 'abjrc_chdc_hyku_abv_ytr_DCD_HTR_TBF'
Declare @NewString Varchar(max) = @string
Declare @Find4thPart Varchar(max) =''

DECLARE @Sep    VARCHAR(10)='_'
DECLARE @Pos    INT=4

WHILE @Pos>0
BEGIN
SET @NewString = SUBSTRING(@NewString, CHARINDEX(@Sep,@NewString)+1,100)

IF @Pos=2
SET @Find4thPart = SUBSTRING(@NewString,1, CHARINDEX(@Sep,@NewString)-1)

SET @Pos=@Pos-1
--SELECT @NewString 
END

seLECT @NewString ,@Find4thPart

Extract characters after character '_' in string

2 Answers2