March 2, 2020 at 1:29 pm
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
March 2, 2020 at 1:33 pm
You may want to consider refining the PATINDEX argument to search for the 4 digits too:
'%XX[0-9][0-9][0-9][0-9]%'
March 2, 2020 at 1:38 pm
working beautifully thanks!
March 2, 2020 at 2:04 pm
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?
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');
March 2, 2020 at 2:46 pm
@phil-parkin perfect
thanks a lot
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy