SQL parse Question 1

[Palmyra]

Can I search for specifics within a string and parse out the data?

For example, where, in the first 12 positions of a string, this appears - ###.###.####, I need to parse out ###, ### and ####.

Thanks.

 

[markros]

Take a look at ParseName function if your data always look like this.

PluralSight Learning Library

 

[Palmyra]

SELECT SUBSTRING(fdesc1,1,4) AS field1,SUBSTRING(fdesc1,6,3) AS field2,SUBSTRING(fdesc1,10,4) AS field3, flocation, fbarcode, fdesc1
FROM folder
WHERE SUBSTRING(fdesc1,1,13) like '%%%.%%%.%%%%%'

returns the data I'm looking for, e.g., where fdesc1 begins with '0100.016.009P', but it also returns data where fdesc1 begins with 'P.S. 89'

 

[gmmastros]

You should use the underscore character instead of %. % means... look for any number of characters. _ means, any single character. Your where clause would return any rows with 2 dots anywhere in the data.

Ex:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('0100.016.009P')
Insert Into @Temp Values('P.S. 89')
Insert Into @Temp Values('..')
Insert Into @Temp Values('.')

Select * From @Temp
Where Data Like '%%%.%%%.%%%%%'

Select * From @Temp
Where Data Like '%___.___.____%'

Copy paste that to a query window and run it. The first query is using your original where clause. The second one uses the underscores.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Palmyra]

Thank you. What if I wanted to search for

Where Data Like '%___.___.____%' where the underscore represented only numbers and not characters?

 

[gmmastros]

You can use a range of characters by using square brackets and a range, like this...

[tt]Where Data Like '%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][a-f]%'[/tt]

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('0100.016.009P')
Insert Into @Temp Values('ABC.DEF.009P')

Insert Into @Temp Values('P.S. 89')
Insert Into @Temp Values('..')
Insert Into @Temp Values('.')

Select * From @Temp
Where Data Like '%___.___.____%'

Select * From @Temp 
Where Data Like '%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][a-z]%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Palmyra]

Perfect, thank you.

 

[danvlas]

George, the first one's from me.



Daniel Vlas
Systems Consultant

 

[gbaughma]

Select * From @Temp
Where Data Like '%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][a-z]%'

Interesting.....

What if I had a field, SampleNum, with records like this:

12345
12345 MS
12345 MSD
12345 LCS
12346
12346 LCS
12346 LCSD

... and I wanted the "MS" and the "LCS"'s?

I tried "SELECT * FROM Sample WHERE SampleNum LIKE '%[ MS][ LCS]' but that didn't do it.

Is there a "wildcard" way to do that, besides

"SELECT * FROM Sample WHERE SampleNum LIKE '% MS' OR SampleNum LIKE '% LCS' ?



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com