Extract string from string that contains specific first two characters

Question

Post reply

Extract string from string that contains specific first two characters

d_martins

Old Hand

Points: 383
More actions
March 2, 2020 at 1:14 pm
Go to Answer
#3730119

Hi
I would like to extract from a full string the 'XX....' string. This specific string inst in the same position in my full string.
- this string always starts with XX
- this string always contains 6 characters

thanks in advance,

Phil Parkin

SSC Guru

Points: 247159

March 2, 2020 at 2:19 pm

Answer

#3730152

d_martins wrote:

For personal knowledge, if the string doesn't always start by XX, but also with AA or BB or CC - how can I extract the string?

Use this as your PATINDEX argument to get close

%[ABX][ABX][0-9][0-9][0-9][0-9]%

This works, but would also find strings that start AB, AX, XA etc.

To get only AA, BB, CC and XX would require something like the above, with a WHERE clause added:

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
    Dsc VARCHAR(100)
);

INSERT #SomeTab
(
    Dsc
)
VALUES
('J-JIM Verification for XX0055')
,('J-JIM XX0022 Verified')
,('J-JIM AA0022 Verified')
,('J-JIM BB0022 Verified')
,('J-JIM AB0022 Verified');

SELECT st.Dsc
      ,p.pat
FROM #SomeTab st
    CROSS APPLY
(
    SELECT pat = SUBSTRING(st.Dsc, PATINDEX('%[ABX][ABX][0-9][0-9][0-9][0-9]%', st.Dsc), 6)
) p
WHERE LEFT(p.pat, 2) IN ('AA', 'BB', 'XX');

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

debasis.yours Hall of Fame Points: 3538 More actions · Answer 1

Hi,

Does this serve your purpose?

;WITH TestString(Test) AS
(SELECT 'J-JIM VERIFICATION FOR XX0055' AS Test UNION ALL
 SELECT 'J-JIM VERIFICATION FOR XX0044' UNION ALL
 SELECT 'J-JIM VERIFICATION FOR XX0033' UNION ALL
 SELECT 'J-JIM XX0022 VERIFIED' UNION ALL
 SELECT 'J-JIM XX0011 VERIFIED')
SELECT SUBSTRING(Test, PATINDEX('%XX%', Test), 6) FROM TestString