Select part of a string

Dan Shepherd

unread,

Sep 1, 2006, 12:12:01 PM9/1/06

to

I have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:

245 HELIUM, COMPRESSED 2.2 UN1046

I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?

SQL Menace

unread,

Sep 1, 2006, 12:37:24 PM9/1/06

to

Don't know if you have to go this crazy way (since I don't know the
formatting of your data)
This assumes that there is always a space between 2.2 UN1046' and also
always a space before 2.2

but here it is anyway, have fun

declare @v varchar(66)
select @v = '245 HELIUM, COMPRESSED 2.2 UN1046'

SELECT right(@v,PATINDEX('% %', reverse(@v))-1) as col1,
right(left(@v,(len(@v)-PATINDEX('% %', reverse(@v)))),(PATINDEX('% %',
ltrim(reverse(left(@v,(len(@v)-PATINDEX('% %', reverse(@v))))))))) as
col2
GO

declare @v varchar(66)
select @v = '24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99'

SELECT right(@v,PATINDEX('% %', reverse(@v))-1) as col1,
right(left(@v,(len(@v)-PATINDEX('% %', reverse(@v)))),(PATINDEX('% %',
ltrim(reverse(left(@v,(len(@v)-PATINDEX('% %', reverse(@v))))))))) as
col2

Denis the SQL Menace
http://sqlservercode.blogspot.com/

ML

unread,

Sep 1, 2006, 12:37:01 PM9/1/06

to

Try this:

declare @str varchar(2000)

set @str = '245 HELIUM, COMPRESSED 2.2 UN1046'

select ltrim(rtrim(left(ltrim(rtrim(substring(@str, charindex('.', @str) -
3, datalength(@str)))), charindex(' ', ltrim(rtrim(substring(@str,
charindex('.', @str) - 3, datalength(@str)))))))) as Digit
,ltrim(rtrim(substring(ltrim(rtrim(substring(@str, charindex('.', @str) -
3, datalength(@str)))), charindex(' ', ltrim(rtrim(substring(@str,
charindex('.', @str) - 3, datalength(@str))))),
datalength(ltrim(rtrim(substring(@str, charindex('.', @str) - 3,
datalength(@str)))))))) as UN_Number

Looks ugly, but works. Assuming Digit (2.2) starts with up to three
characters (i.e. a maximum of three characters before the dot). If you need
more replace 3 with a more appropriate length.

ML

---
http://milambda.blogspot.com/

Bob

unread,

Sep 1, 2006, 12:38:02 PM9/1/06

to

Try:

DROP TABLE #tmp
CREATE TABLE #tmp ( test_string VARCHAR(50) )

INSERT INTO #tmp VALUES ( '245 HELIUM, COMPRESSED 2.2 UN1046' )

-- If you know where the columns always start like in a fixed-width file,
it's easy with SUBSTRING
SELECT SUBSTRING( test_string, 30, 3 ), SUBSTRING( test_string, 35, 6 )
FROM #tmp

-- If you don't know, then it could be tricky, but lookup PATINDEX and
CHARINDEX
SELECT PATINDEX( '%[0-9].[0-9]%', test_string ) AS UN_number_start
FROM #tmp

-- and nesting them in SUBSTRING
SELECT SUBSTRING( test_string, PATINDEX( '%[0-9].[0-9]%', test_string ), 3
), SUBSTRING( test_string, PATINDEX( '%[0-9].[0-9]%', test_string ) + 5, 6 )
FROM #tmp

Hope that helps

WBob

SQL Menace

unread,

Sep 1, 2006, 12:43:25 PM9/1/06

to

Between your solution and mine I have never seen so many parenthese in
my life ;-)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

ML

unread,

Sep 1, 2006, 12:50:02 PM9/1/06

to

Yes, it looks ridiculous. :)

Reminds me of a few games I've played on ye olde Spectrum.

ML

---
http://milambda.blogspot.com/

Dan Shepherd

unread,

Sep 1, 2006, 1:47:01 PM9/1/06

to

That helps me understand and I think it will work but need one additional
piece of information / help.

My table has the following columns.. ItemDescription and ItemNumber and I am
parsing the ItemDescription.. So how do I declare the variable and loop
through the table?

I tried using your script but changed the select statement to be:
select @v = (select ItemDescription from Inventory where ItemDescription
like '% UN%')
What am I missing?

I don't know how to do it.

SQL Menace

unread,

Sep 1, 2006, 1:58:16 PM9/1/06

to

Here we go

SELECT right(ItemDescription,PATINDEX('% %',
reverse(ItemDescription))-1) as col1,
right(left(ItemDescription,(len(ItemDescription)-PATINDEX('% %',
reverse(ItemDescription)))),(PATINDEX('% %',
ltrim(reverse(left(ItemDescription,(len(ItemDescription)-PATINDEX('%
%', reverse(ItemDescription))))))))) as
col2

from Inventory where ItemDescription
like '% UN%'

Denis the SQL Menace

Steve Dassin

unread,

Sep 1, 2006, 3:06:44 PM9/1/06

to

Reminds me of the song:
'Will she still love me TOMORROW'

:)

http://racster.blogspot.com

"SQL Menace" <denis...@gmail.com> wrote in message
news:1157133496.6...@m73g2000cwd.googlegroups.com...

Arnie Rowland

unread,

Sep 1, 2006, 3:24:34 PM9/1/06

to

OK, enough already. You have exhausted your allotment of parentheses for the
day...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"SQL Menace" <denis...@gmail.com> wrote in message
news:1157133496.6...@m73g2000cwd.googlegroups.com...

Arnie Rowland

unread,

Sep 1, 2006, 3:25:06 PM9/1/06

to

It's time for a Haiku!

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"ML" <M...@discussions.microsoft.com> wrote in message
news:33305B11-CB2D-4943...@microsoft.com...

ML

unread,

Sep 2, 2006, 5:46:02 AM9/2/06

to

5-7-5?

Warm these autumn winds
Mystic chants bring happiness
In parentheses

ML

---
http://milambda.blogspot.com/

ML

unread,

Sep 2, 2006, 5:47:02 AM9/2/06

to

For the day? For 2006 more likely... :)

ML

---
http://milambda.blogspot.com/

Hugo Kornelis

unread,

Sep 3, 2006, 4:26:18 PM9/3/06

to

On 1 Sep 2006 09:43:25 -0700, SQL Menace wrote:

>Between your solution and mine I have never seen so many parenthese in
>my life ;-)

Hi Denis,

Try writing a program in LISP :-)

--
Hugo Kornelis, SQL Server MVP