|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Parse Text ProblemI'm at a standstill on this and hoping that someone can assist. I've looked at substring, trim, right, and left to no avail. I have a field called Jobname this field can contain different lengths of text. Here are some examples 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 What I need to do is retrieve the data to the right of the "U." but it's not that easy. The first 2 charaters after the "U." I don't need, the 4 numbers after that I do need. Here's the tricky part; the first letter after the 4 numbers I need if they meet the following condition - the 4 numbers are not followed by MV, howver if it's followed by an MM I need it. The result set from above would be 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 = 7829 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 = 9512 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 = 9218F 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 = 9945Z 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 = 9945M I'm clueless. Thanks in advance. Jason Use pubs
Set NoCount On go Create Table Foo (MyData varchar(100)) Insert Foo (MyData) Values ( '3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253') Insert Foo (MyData) Values ( '3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253') Insert Foo (MyData) Values ( '3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252') Insert Foo (MyData) Values ( '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253') Insert Foo (MyData) Values ( '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254') Select Case When TempResult Like '____MV%' Then Left(TempResult, 4) Else Left(TempResult, 5) End As Result From (Select Substring(MyData, CharIndex('U.', MyData) + 4, Len(MyData)) As TempResult From Foo) x go Drop Table Foo Tom <kaplan.ja***@gmail.com> wrote in message Show quote news:1155848704.289736.228020@i42g2000cwa.googlegroups.com... > Hi, > > I'm at a standstill on this and hoping that someone can assist. I've > looked at substring, trim, right, and left to no avail. > > I have a field called Jobname this field can contain different lengths > of text. Here are some examples > > 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 > 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 > 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 > > What I need to do is retrieve the data to the right of the "U." but > it's not that easy. The first 2 charaters after the "U." I don't need, > the 4 numbers after that I do need. Here's the tricky part; the first > letter after the 4 numbers I need if they meet the following condition > - the 4 numbers are not followed by MV, howver if it's followed by an > MM I need it. > > The result set from above would be > > 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 = 7829 > 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 = 9512 > 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 = 9218F > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 = 9945Z > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 = 9945M > > I'm clueless. Thanks in advance. > > Jason > Thanks for the reply.
Can I use the SELECT Case statement to query the entire table that holds this info? I'm not sure what the syntax is doing. It appears to be creating a temp table with the data I supplied, correct? Tom Cooper wrote: Show quote > Use pubs > Set NoCount On > go > Create Table Foo (MyData varchar(100)) > Insert Foo (MyData) Values ( > '3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253') > Insert Foo (MyData) Values ( > '3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253') > Insert Foo (MyData) Values ( > '3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252') > Insert Foo (MyData) Values ( > '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253') > Insert Foo (MyData) Values ( > '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254') > Select Case > When TempResult Like '____MV%' Then Left(TempResult, 4) > Else Left(TempResult, 5) > End As Result > From (Select Substring(MyData, CharIndex('U.', MyData) + 4, Len(MyData)) As > TempResult > From Foo) x > go > Drop Table Foo > > Tom > > <kaplan.ja***@gmail.com> wrote in message > news:1155848704.289736.228020@i42g2000cwa.googlegroups.com... > > Hi, > > > > I'm at a standstill on this and hoping that someone can assist. I've > > looked at substring, trim, right, and left to no avail. > > > > I have a field called Jobname this field can contain different lengths > > of text. Here are some examples > > > > 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 > > 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 > > 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 > > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 > > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 > > > > What I need to do is retrieve the data to the right of the "U." but > > it's not that easy. The first 2 charaters after the "U." I don't need, > > the 4 numbers after that I do need. Here's the tricky part; the first > > letter after the 4 numbers I need if they meet the following condition > > - the 4 numbers are not followed by MV, howver if it's followed by an > > MM I need it. > > > > The result set from above would be > > > > 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 = 7829 > > 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 = 9512 > > 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 = 9218F > > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 = 9945Z > > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 = 9945M > > > > I'm clueless. Thanks in advance. > > > > Jason > > Sorry, if that was confusing. I just loaded your data into a table for me
to test. The part of it you want is: Select Case When TempResult Like '____MV%' Then Left(TempResult, 4) Else Left(TempResult, 5) End As Result From (Select Substring(Jobname, CharIndex('U.', Jobname) + 4, Len(Jobname)) As TempResult From Foo) x (of course, you need to replace Foo with the name of your table). Tom <kaplan.ja***@gmail.com> wrote in message Show quote news:1155850466.856555.95330@74g2000cwt.googlegroups.com... > Thanks for the reply. > > Can I use the SELECT Case statement to query the entire table that > holds this info? I'm not sure what the syntax is doing. It appears to > be creating a temp table with the data I supplied, correct? > > > Tom Cooper wrote: >> Use pubs >> Set NoCount On >> go >> Create Table Foo (MyData varchar(100)) >> Insert Foo (MyData) Values ( >> '3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253') >> Insert Foo (MyData) Values ( >> '3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253') >> Insert Foo (MyData) Values ( >> '3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252') >> Insert Foo (MyData) Values ( >> '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253') >> Insert Foo (MyData) Values ( >> '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254') >> Select Case >> When TempResult Like '____MV%' Then Left(TempResult, 4) >> Else Left(TempResult, 5) >> End As Result >> From (Select Substring(MyData, CharIndex('U.', MyData) + 4, Len(MyData)) >> As >> TempResult >> From Foo) x >> go >> Drop Table Foo >> >> Tom >> >> <kaplan.ja***@gmail.com> wrote in message >> news:1155848704.289736.228020@i42g2000cwa.googlegroups.com... >> > Hi, >> > >> > I'm at a standstill on this and hoping that someone can assist. I've >> > looked at substring, trim, right, and left to no avail. >> > >> > I have a field called Jobname this field can contain different lengths >> > of text. Here are some examples >> > >> > 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 >> > 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 >> > 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 >> > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 >> > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 >> > >> > What I need to do is retrieve the data to the right of the "U." but >> > it's not that easy. The first 2 charaters after the "U." I don't need, >> > the 4 numbers after that I do need. Here's the tricky part; the first >> > letter after the 4 numbers I need if they meet the following condition >> > - the 4 numbers are not followed by MV, howver if it's followed by an >> > MM I need it. >> > >> > The result set from above would be >> > >> > 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253 = 7829 >> > 3770732:lsfadmin:IVUSUPDATEMANPROD:ivbld:U.H59512MVUSP00253 = 9512 >> > 3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252 = 9218F >> > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253 = 9945Z >> > 3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254 = 9945M >> > >> > I'm clueless. Thanks in advance. >> > >> > Jason >> > > Thanks again.
Rick Sawtell's answer worked with no problems. However I'm getting an error when using Tom's. The error is Line 3: Incorrect syntax near ')'. I'm currently troubleshooting it myself and will reply back if I find out why before someone else does. I appreciate the help you both gave and the explanation of what each step does. I just don't like getting the answers without actually knowing what the code is doing. This is why it's helpful to get the breakdown. Thanks again both of you. I'm looking into Tom's code to see where it's failing. Jason Try this:
CREATE TABLE Foo ( Jobname varchar(100) ) GO -- Put some dummy rows in INSERT Foo SELECT '3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP00253' INSERT Foo SELECT '3777207:lsfadmin:IVUSBUILDMANPROD:ivbld:U.T99218FFMVUSP00252' INSERT Foo SELECT '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945ZZMVUSP00253' INSERT Foo SELECT '3787149:lsfadmin:IVUSBUILDSCNMANPROD:ivbld:U.PX9945MMMVUSP00254' -- Use the CharIndex to find the ivbld starting point. -- Use the Substring, to pull the ivbld value on out the 2 digits which tell us to get the letter or not -- Use the RIGHT, to get just those two letters that tell us whether or not it is an MV -- If it is an MV, then pull the same substring from only 14 characters out and grab 4 (just the numbers) -- else grab 15 characters out and grab the numbers and the letter. SELECT CASE RIGHT(SUBSTRING(JobName, CHARINDEX('ivbld:U.', Jobname, 1), 16), 2) WHEN 'MV' THEN RIGHT(SUBSTRING(JobName, CHARINDEX('ivbld:U.', Jobname, 1), 14), 4) ELSE RIGHT(SUBSTRING(JobName, CHARINDEX('ivbld:U.', Jobname, 1), 15), 5) END FROM Foo -- cleanup after ourselves DROP TABLE Foo Rick Sawtell MCT, MCSD, MCDBA  |
|||||||||||||||||||||||
Other interesting topics