How to grab some code from this string

Transact-SQL
1

I have this long string that exists in a table and i am trying to get the
S~CP2EMPCAD_T6.DAT
Which is the X_INPUT_FILE_S name value

INDICATOR_USE~A~ADP_HIRE_DT_LOCADP_BIRTH_DT_LOCUSE_STD_HRS_FL~Y~MAP_PAYSERVID_FL~N~VENDOR_FL~Y
~ALT_FILE_LOCATION~WD_EMP~X_INPUT_FILE_S~CP2EMPCAD_T6.DAT~

2

You need to use a string splitter function. If using SQL2022 STRING_SPLIT will be okay as you will need enable_ordinal otherwise download something like:

Tally OH! An Improved SQL 8K “CSV Splitter” Function – SQLServerCentral

The odd numbers will then be the keys with the corresponding even numbers the values.

3

You can use CHARINDEX and SUBSTRING function. Like this example:

DECLARE @String VARCHAR(255);

SET @String='INDICATOR_USE~A~ADP_HIRE_DT_LOCADP_BIRTH_DT_LOCUSE_STD_HRS_FL~Y~MAP_PAYSERVID_FL~N~VENDOR_FL~Y
~ALT_FILE_LOCATION~WD_EMP~X_INPUT_FILE_S~CP2EMPCAD_T6.DAT~';

SELECT @String;

SELECT CHARINDEX('X_INPUT_FILE_S',@String)

SELECT SUBSTRING(@String,CHARINDEX('X_INPUT_FILE_S',@String),LEN(@String) +1 -CHARINDEX('X_INPUT_FILE_S',@String));
4

This looks great, what do i amend just to get the CP2EMPCAD_T6.DAT value only.

5

DECLARE @String VARCHAR(255);

SET @String='INDICATOR_USE~A~ADP_HIRE_DT_LOCADP_BIRTH_DT_LOCUSE_STD_HRS_FL~Y~MAP_PAYSERVID_FL~N~VENDOR_FL~Y
~ALT_FILE_LOCATION~WD_EMP~X_INPUT_FILE_S~CP2EMPCAD_T6.DAT~';

SELECT @String;

SELECT CHARINDEX('X_INPUT_FILE_S',@String)

SELECT SUBSTRING(@String,CHARINDEX('X_INPUT_FILE_S',@String) +15 , LEN(@String) - (CHARINDEX('X_INPUT_FILE_S',@String)) - 15);

1 Like
6

Thank you so much. I appreciate it.