t-sql substring to return right 6/7 characters after value in a Free Text field

Question

Post reply

t-sql substring to return right 6/7 characters after value in a Free Text field

Daniel Taylor-446457

Mr or Mrs. 500

Points: 502

January 30, 2024 at 11:50 am

Go to Answer

#4370097

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 ''

frederico_fonseca

SSCoach

Points: 16156

January 30, 2024 at 1:14 pm

Answer

#4370138

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 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

DesNorton SSC-Insane Points: 24387 More actions · Answer 1

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 ...

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

... 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

Daniel Taylor-446457 Mr or Mrs. 500 Points: 502 More actions · Answer 3

😂 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

Daniel Taylor-446457 Mr or Mrs. 500 Points: 502 More actions · Answer 4

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.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

Daniel Taylor-446457 wrote:

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

Daniel Taylor-446457 Mr or Mrs. 500 Points: 502 More actions · Answer 6

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?

Daniel Taylor-446457 Mr or Mrs. 500 Points: 502 More actions · Answer 7

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

frederico_fonseca SSCoach Points: 16156 More actions · Answer 8

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

Daniel Taylor-446457 Mr or Mrs. 500 Points: 502 More actions · Answer 9

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%'

ScottPletcher SSC Guru Points: 101103 More actions · Answer 10

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".