Hi i have a t-sql puzzle i'm hoping someone can help with.
We have a 'free text' notes field that sometimes contains a CustID. I would like to extract this Value.
The CustID always starts with 'FC' or ' FC' or 'FC0'. And is typically 6 or 7 numbers long.
I would love to extract the CustID from the string to help attach the notes to our Customers table.
Please could one of you SQL gurus work your magic?
Sample TSQL below
--=============================
--DROP objects if they exist
--=============================
IF OBJECT_ID('dbo.testdata', 'U') IS NOT NULL
DROP TABLE dbo.testdata;
--=============================
--Create test data table & Data
--=============================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].testdata(
[freetext] varchar (200)NOT NULL,
) ON [PRIMARY]
GO
INSERT [dbo].testdata ([freetext]) VALUES ('abcd ksks - FC27957 dhdjkd')
GO
INSERT [dbo].testdata ([freetext]) VALUES ('Gddd eftdgfd 10,968.00 FC30920')
GO
INSERT [dbo].testdata ([freetext]) VALUES ('fffegfdgd shsh FC020022')
GO
INSERT [dbo].testdata ([freetext]) VALUES ('beefcake FC03245 fhfh')
GO
INSERT [dbo].testdata ([freetext]) VALUES ('fffegfdgd')
GO
--=============================
--sample of desired output
--=============================
select '27957'
select '30920'
select '20022'
select '3245'
select ''
January 30, 2024 at 12:38 pm
Rather have your UI pass the CustomerID from it's own context when passing the note to be saved. Shredding free text is expensive and will definitely come back to bite you in the ...
January 30, 2024 at 12:45 pm
... future? 😀
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
DROP TABLE if exists #testdata;
--=============================
--Create test data table & Data
--=============================
CREATE TABLE #testdata
(
[freetext] varchar (200)NOT NULL,
)
;
insert into #testdata
select *
from (values ('abcd ksks - FC27957 dhdjkd')
, ('Gddd eftdgfd 10,968.00 FC30920')
, ('fffegfdgd shsh FC020022')
, ('beefcake FC03245 fhfh')
, ('fffegfdgd')
) t(txt)
select t1.*
, t2.ordinal
, t2.value
, substring(t2.value, 3,200) as CustID
from #testdata t1
outer apply string_split(t1.[freetext], ' ', 1) t2
where t2.value like 'FC%'
January 30, 2024 at 1:23 pm
😂 yes i can only imagine a future with a resolution
This is a 1 off exercise outside of any UI to join some legacy data. Im less worried about cost, in this instance.
This was as close as i could get but the first ' ' (space) or tripped it up .. if u run results u would see.
SELECT [freetext]
, RIGHT([freetext],CHARINDEX(' FC',[freetext]))
FROM testdata
January 30, 2024 at 1:32 pm
Thanks. This looks good. Haddnt stumbled on split_string
When i run code on my machine it returns?
Msg 8144, Level 16, State 3, Line 27
Procedure or function string_split has too many arguments specified.
January 30, 2024 at 1:34 pm
Thanks. This looks good. Haddnt stumbled on split_string
When i run code on my machine it returns?
Msg 8144, Level 16, State 3, Line 27 Procedure or function string_split has too many arguments specified.
You have posted in a SQL Server 2022 forum. Are you using an earlier version? Earlier versions of SQL Server do not support the third argument of String_Split().
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 30, 2024 at 1:48 pm
Good point. It should be on 2017 forum. Sorry about that. 😕
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169
I did check after error & found when split_string was introduced ... and it : SQL Server 2016 (13.x) and later
...so should be ok. assume it runs without error on yours?
January 30, 2024 at 1:52 pm
sorry - jumping around here. You are right - its the 3rd argument.
enable_ordinal it doesnt support.
Sorry to waste people time 😔
Will leave question on forum may well be useful for those on 2022.
Thanks
January 30, 2024 at 2:26 pm
on this case you don't need the third argument so just remove it (as well as the t2.ordinal from the select) and it should work as you intended.
you may need to watch out for cases where 2 or more FC... are on same column
January 30, 2024 at 3:11 pm
amazing. Yes works for what i need. big thanks!
DROP TABLE if exists #testdata;
--=============================
--Create test data table & Data
--=============================
CREATE TABLE #testdata
(
[freetext] varchar (200)NOT NULL,
)
;
insert into #testdata
select *
from (values ('abcd ksks - FC27957 dhdjkd')
, ('Gddd eftdgfd 10,968.00 FC30920')
, ('fffegfdgd shsh FC020022')
, ('beefcake FC03245 fhfh')
, ('fffegfdgd')
) t(txt)
select t1.*
-- , t2.ordinal
, t2.value
, substring(t2.value, 3,200) as CustID
from #testdata t1
outer apply string_split(t1.[freetext], ' ') t2
where t2.value like 'FC%'
January 30, 2024 at 5:21 pm
SELECT
[freetext],
CASE WHEN FC_start = 0 THEN '' ELSE RTRIM(SUBSTRING([freetext], FC_start, FC_length)) END AS value
FROM dbo.testdata
CROSS APPLY (
SELECT PATINDEX('%[^A-Z]FC%', [freetext]) + 1 AS FC_start
) AS ca1
CROSS APPLY (
SELECT CHARINDEX(' ', SUBSTRING([freetext] + ' ', FC_start, 9)) AS FC_length
) AS ca2
--WHERE [freetext] LIKE '%FC%'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 31, 2024 at 9:47 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply