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_LOC USE_STD_HRS_FL~Y~MAP_PAYSERVID_FL~N~VENDOR_FL~Y
~ALT_FILE_LOCATION~WD_EMP~X_INPUT_FILE_S~CP2EMPCAD_T6.DAT~
Ifor
July 11, 2024, 6:50am
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.
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));
RogierPronk:
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));
This looks great, what do i amend just to get the CP2EMPCAD_T6.DAT value only.
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
Thank you so much. I appreciate it.