Break out a description field.

Question

Break out a description field.

bpowers

SSCertifiable

Points: 5483
More actions
February 18, 2009 at 10:22 am

#129135

I have a table with a string field in which I want to pull out the numbers at the end. Anyone know how I could accomplish this?
Inventory_Trans.Description
Transfer to location S034R6/MAIN RCVR ID 55310-1
Transfer to location E/MACHINE SHOP RCVR ID 55334-1
Transfer to location F/MACHINE SHOP RCVR ID 55329-1
I want to pull out the receiver id at the end of the string (i.e. 55310-1).
Any help will be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

If its always present and the same size use the right() function with the size of that RIGHT(column-name, 7) in your examples.

If its not the same size then use charindex() or some other method to identify the starting poistion by searching for " id " or some other key value.

Lowell SSC Guru Points: 323518 More actions · Answer 2

if you add a tally table, this is one of those magical Tally table wonders that extracts between two delimiters: it pulls out the ID and each of the Id's as a row of data.Note how ID 1 has 3 "ID's but id 2 has 6; it takes into consideration the 1:many thang your descriptions field probably has.

[font="Courier New"]

CREATE TABLE #Inventory_Trans (OtherId INT,Description VARCHAR(MAX) )

INSERT INTO #Inventory_Trans

SELECT 1,'Transfer to location S034R6/MAIN RCVR ID 55310-1

Transfer to location E/MACHINE SHOP RCVR ID 55334-1

Transfer to location F/MACHINE SHOP RCVR ID 55329-1'

UNION ALL

SELECT 2,'Transfer to location S034R6/MAIN RCVR ID 77310-1

Transfer to location E/MACHINE SHOP RCVR ID 77334-1

Transfer to location E/MACHINE SHOP RCVR ID 6544-1

Transfer to location E/MACHINE SHOP RCVR ID 77378-1

Transfer to location E/MACHINE SHOP RCVR ID 77334-9

Transfer to location F/MACHINE SHOP RCVR ID 77329-1'

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

DECLARE

@pre VARCHAR(10),

@post VARCHAR(10),

@pr INT,

@po INT,

@st INT

SET @pre = 'ID '

SET @post = 'Transfer'

SET @pr = LEN(@pre)

SET @po = LEN(@post)

SET @st = 1

SELECT TOP 30

T1.N,

T2.*,

SUBSTRING( T2.Description,

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

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

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

Description

FROM Tally T1

CROSS APPLY #Inventory_Trans T2

WHERE N <= LEN( T2.Description )

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

[/font]

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!

noeld SSC Guru Points: 96590 More actions · Answer 3

DECLARE @Inventory_Trans TABLE (Id INT,Description VARCHAR(800) )

INSERT INTO @Inventory_Trans ( Id, Description )

SELECT 1,'Transfer to location S034R6/MAIN RCVR ID 55310-1'

UNION ALL SELECT 2,'Transfer to location E/MACHINE SHOP RCVR ID 55334-1'

UNION ALL SELECT 3,'Transfer to location F/MACHINE SHOP RCVR ID 55329-1'

SELECT Id, SUBSTRING(Description,CHARINDEX('RCVR ID',Description)+9, LEN(Description)- CHARINDEX('RCVR ID',Description) - 8) as Receiver

FROM @Inventory_Trans

* Noel

bpowers SSCertifiable Points: 5483 More actions · Answer 4

bpowers

SSCertifiable

Points: 5483

February 18, 2009 at 11:57 am

#946190

That worked. Thank you very much!