Extract a Value in a str

Question

Extract a Value in a str

mranganwa

SSC Enthusiast

Points: 125
More actions
April 14, 2010 at 9:49 am

#140377

I have string in which I want to extract a TBM number. There can be more than one occurence in a string and I want to extract all distinct occurances. I have written a script but its not extracting everything. How can I modify this ?
--Create Table
IF OBJECT_ID('TEMPDB..#Test') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test (String VARCHAR(200))
--- Insert Data
INSERT #Test VALUES('TBM928')--- should extract the value TBM928
INSERT #Test VALUES('TBM419 - ODT requested') --- should extract the value TBM419
INSERT #Test VALUES('TBM924')
INSERT #Test VALUES('TBM923')
INSERT #Test VALUES('TBM920')
INSERT #Test VALUES('TBM915')
INSERT #Test VALUES('TBM910 - 03/02: re-submitted with amended address')--- should extract the value TBM910
INSERT #Test VALUES('TBM907 - 16/03: provided a new ,of TBM908 - 02/03: TBM908 has TBM908')--- should extract the values TBM907,TBM908
INSERT #Test VALUES('TBM900 - 05/02: cancelled X234156 due to B side address changes')--- should extract the value TBM900
INSERT #Test VALUES('TBM897 - 02/02: ref not supplied, client need to confirm site')--- should extract the value TBM419
INSERT #Test VALUES('TBM909')
INSERT #Test VALUES('TBM906 - 03/02: re-submitted with amended address')--- should extract the value TBM419
INSERT #Test VALUES('TBM904 - 03/02: re-submitted with amended address')--- should extract the value TBM419
INSERT #Test VALUES('TBM903 - 03/02: re-submitted with amended address')--- should extract the value TBM419
INSERT #Test VALUES('TBML898 - 02/02: Ref not supplied, client to confirm')--- should extract the value TBM419
INSERT #Test VALUES('TBM914 - 16/02 rejected by commercial - address amended & resubmitted')--- should extract the value TBM419
--This is what I have and its not extracting everything
SELECT LEFT(String,CASE WHEN CHARINDEX('-',String) = 0 THEN LEN(String) ELSE CHARINDEX('-',String)-1 END) AS NewCol, String
FROM #Test

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if the TBM number is always 6 characters and it always starts the string, you could simply take the LEFT(col,6) of the string

SELECT LEFT(String,6) from #Test

if it could exist anywhere in the string,and is still 6 characters in length, you can use CHARINDEX to determine where to start:

SELECT SUBSTRING(String,CHARINDEX('TBM',String), 6) from #Test

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

oops reread it and there are multiples;

this seems to get you 98% there:

--now get the ID's using a tally table

declare

@pre varchar(10),

@post varchar(10),

@pr int,

@po int,

@st int

set @pre = 'TBM'

set @post = ' '

set @pr = len(@pre)

set @po = len(@post)

set @st = 1

;WITH tally (N) as

(SELECT TOP 1000 row_number() OVER (ORDER BY sc1.id)

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2)

SELECTtop 30

T1.N,

T2.*,

SUBSTRING( T2.String,

( CASE T1.N WHEN 1 THEN 1 ELSE T1.N + @pr END ),

( CASE CHARINDEX( @post, T2.String, T1.N + @pr )WHEN 0 THEN LEN( T2.String ) - T1.N + @pr

ELSE CHARINDEX( @post, T2.String, T1.N + @pr ) - ( T1.N + @pr ) END ) ) AS DesiredString ,

String

FROMTally T1

CROSS APPLY #Test T2

WHEREN <= LEN( T2.String )

AND SUBSTRING( T2.String, T1.N, @pr ) = @pre

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 3

In the next-to-last INSERT, you have TBMLxxx. It also looks like you did a cut-and-paste and have several lines of -- this should return TBM419, when TBM419 is not in that string.

This code will return a table of all of the TBMxxx values from the INSERT statements that you provided.

declare @Delimiter char(3)

set @Delimiter = 'TBM'

-- Create an inline tally table.

-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.

;WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),

Thousands (N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Thousands)

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(String, N, CHARINDEX(@Delimiter, String + @Delimiter, N) - N + 6) AS Item

FROM #Test

CROSS APPLY Tally

WHERE N < LEN(String) + 3

AND SUBSTRING(String, N, LEN(@Delimiter)) = @Delimiter

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mranganwa SSC Enthusiast Points: 125 More actions · Answer 4

Hi

works nicely but when I encounter a string like this one

INSERT #Test VALUES('TBM907 - 16/03: provided a new ,of TBM908 - 02/03: TBM908 has TBM908')--- I want to extract all TBM numberss that are in the string i.e I have to extract the values from this string TBM907,TBM908

INSERT #Test VALUES('TBM - TBM534 cancelled, client moving to new premises') The script is only extracting TBM - instead of TBM534.

WayneS SSC Guru Points: 95460 More actions · Answer 5

mranganwa (4/14/2010)
Hi
works nicely but when I encounter a string like this one
INSERT #Test VALUES('TBM907 - 16/03: provided a new ,of TBM908 - 02/03: TBM908 has TBM908')--- I want to extract all TBM numberss that are in the string i.e I have to extract the values from this string TBM907,TBM908
INSERT #Test VALUES('TBM - TBM534 cancelled, client moving to new premises') The script is only extracting TBM - instead of TBM534.

When I run it against JUST these 2 insert statements, I get:

ItemNumberItem

1TBM907

2TBM -

3TBM534

4TBM908

5TBM908

6TBM908

As you can see, this gets all 6 values entered in these two lines... including the invalid one.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mranganwa SSC Enthusiast Points: 125 More actions · Answer 6

Hi,

What I meant was if I have a Sol_id. How do I put the TBM numbers from the same string as item1,item2 etc but in one row and keeping the same Sol_id ?

IF OBJECT_ID('TEMPDB..#Test') IS NOT NULL

DROP TABLE #Test

GO

CREATE TABLE #Test (Sol_Id varchar(10),String VARCHAR(500))

--- Insert Data

INSERT #Test VALUES('1-101','TBM928')

INSERT #Test VALUES('1-102','TBM419 - ODT requested')

INSERT #Test VALUES('1-103','TBM924')

INSERT #Test VALUES('1-104','TBM923')

INSERT #Test VALUES('1-105','TBM541')

INSERT #Test VALUES('1-106','TBM910 - 03/02: re-submitted with amended address')

INSERT #Test VALUES('1-107','TBM907 - 16/03: Telkom provided a new WORM, Serial & Circuit no. as the previous one''s were a duplicate of TBM908 - 02/03: requested Telkom to provide the correct serial & cct no. as VDL908 has the same serial and cct no. 26/02: Telkom to provide correct serial and cct no. serial 9608786 & cct 52-03600-00 is the same as VDL908')

INSERT #Test VALUES('1-108','TBM902')

INSERT #Test VALUES('1-109','TBM900 - 05/02: Telkom cancelled W991201 due to B side address changes as per Shiam')

INSERT #Test VALUES('1-110','TBM899')

INSERT #Test VALUES('1-111','TBM897 - 02/02: WORM ref not supplied, client need to confirm site address as landline no. plots to 1 Jan Smuts Ave, Kempton')

INSERT #Test VALUES('1-112','TBM906 - 03/02: re-submitted with amended address')

INSERT #Test VALUES('1-113','TBM905')

INSERT #Test VALUES('1-114','TBM904 - 03/02: re-submitted with amended address')

INSERT #Test VALUES('1-115','TBM903 - 03/02: re-submitted with amended address TBM901')

INSERT #Test VALUES('1-116','TBM898 - 02/02: WORM Ref not supplied, client to confirm if address is correct as the landline no. plots to No 1 Thaba Nchu Rd, Bob Rogers Park, Bloemfontein')

INSERT #Test VALUES('1-117','')

INSERT #Test VALUES('1-118','TBM914 - 16/02 WORM rejected by commercial - address amended & resubmitted')

INSERT #Test VALUES('1-119','TBM913 - 16/02 WORM rejected by commercial - address amended & resubmitted')

INSERT #Test VALUES('1-111','TBM879 - 15/01: Client indicated that the link should terminate on onsite node 5286 DXX, re-applied to Telkom with new noded details')

INSERT #Test VALUES('1-112','TBM - TBM534 cancelled, client moving to new premises in Jan 2010, re-apply in Jan')

INSERT #Test VALUES('1-113','TBM401 - 1024k speed on Tx Man')

INSERT #Test VALUES('1-114','TBM885')

INSERT #Test VALUES('1-115','TBM - TBM534 cancelled, client moving to new premises')

WayneS SSC Guru Points: 95460 More actions · Answer 7

Based on this sample data, what do you want the output to look like? I'm just not understanding what it is that you are wanting to get out of this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mranganwa SSC Enthusiast Points: 125 More actions · Answer 8

this is the output I'm looking for. To extract every TBM number for each Sol_Id and have them in the same row as shown below only if they are complete TBM numbers. i.e TBM908.

Sol_IdTBMNoTBMNo2TBMNo3TBMNo4

==============================

1-101TBM928

1-107TBM907TBM908

1-112 TBM534

1-115 TBM534

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

mranganwa (4/19/2010)
this is the output I'm looking for. To extract every TBM number for each Sol_Id and have them in the same row as shown below only if they are complete TBM numbers. i.e TBM908.
Sol_IdTBMNoTBMNo2TBMNo3TBMNo4
==============================
1-101TBM928
1-107TBM907TBM908
1-112 TBM534
1-115 TBM534

How many TBM's can there be for 1 Sol_ID?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mranganwa SSC Enthusiast Points: 125 More actions · Answer 10

mranganwa

SSC Enthusiast

Points: 125

April 20, 2010 at 1:40 am

#1154361

Not more than 3 at the most 4