|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Select part of a stringI 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? 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/ Between your solution and mine I have never seen so many parenthese in
my life ;-) Denis the SQL Menace http://sqlservercode.blogspot.com/ ML wrote: Show quote > 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/ Yes, it looks ridiculous. :)
Reminds me of a few games I've played on ye olde Spectrum. ML --- http://milambda.blogspot.com/ It's time for a Haiku!
-- Show quoteArnie 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-B65A-30FEA3F3DDD0@microsoft.com... > Yes, it looks ridiculous. :) > > Reminds me of a few games I've played on ye olde Spectrum. > > > ML > > --- > http://milambda.blogspot.com/ 5-7-5?
Warm these autumn winds Mystic chants bring happiness In parentheses ML --- http://milambda.blogspot.com/ On 1 Sep 2006 09:43:25 -0700, SQL Menace wrote:
>Between your solution and mine I have never seen so many parenthese in Hi Denis,>my life ;-) Try writing a program in LISP :-) -- Hugo Kornelis, SQL Server MVP 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/ Dan Shepherd wrote: Show quote > 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? 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. Show quote "SQL Menace" wrote: > 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/ > > > Dan Shepherd wrote: > > 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? > > 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 http://sqlservercode.blogspot.com/ Dan Shepherd wrote: Show quote > 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" wrote: > > > 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/ > > > > > > Dan Shepherd wrote: > > > 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? > > > > Reminds me of the song:
'Will she still love me TOMORROW' :) http://racster.blogspot.comShow quote "SQL Menace" <denis.g***@gmail.com> wrote in message news:1157133496.620941.324040@m73g2000cwd.googlegroups.com... > 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 > http://sqlservercode.blogspot.com/ OK, enough already. You have exhausted your allotment of parentheses for the
day... -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "SQL Menace" <denis.g***@gmail.com> wrote in message news:1157133496.620941.324040@m73g2000cwd.googlegroups.com... > 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 > http://sqlservercode.blogspot.com/ > > > > Dan Shepherd wrote: >> 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" wrote: >> >> > 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/ >> > >> > >> > Dan Shepherd wrote: >> > > 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? >> > >> > > 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 Show quote "Dan Shepherd" wrote: > 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?  |
|||||||||||||||||||||||
Other interesting topics