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~
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));
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);
Thank you so much. I appreciate it.
Hi
Hope this helps
This is using string split and ordinal
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;
; WITH cte as
(SELECT
value
,lead(value) Over(order by ordinal) as ldvalue
FROM
string_split(@string,'~',1))
SELECT
*
FROM
cte
WHERE
value like '%X_INPUT_FILE_S%'
Thank you
