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: 246985
More actions
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');
```
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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

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

debasis.yours Hall of Fame Points: 3465 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