Solved

T SQL search instring

Posted on 2013-01-19
2
349 Views
Last Modified: 2013-01-19
I have a field containing a coded string, part of which appears like this (there is other string data in the field either side of the below examples)...

foo: 0,0,3,0;
bar: 0,0,2,0;
foo: 0,2,1,0;
bar: 0,0,0,0;
foo: 1,2,3,1;
bar: 0,1,8,0;

What I need to do is select all rows that contain a value greater than 2 in the 3rd of the 4 comma-separated numbers. In other words, to return only the following rows from the above example...

foo: 0,0,3,0;
bar: 0,0,2,0;
foo: 1,2,3,1;
bar: 0,1,8,0;

How would the query be constructed to achieve this?

I'm thinking along the lines of...

select * from mytable where
(myfield like '%foo: [0-10],[0-10],[3-10],[0-10];%') OR
(myfield like '%bar: [0-10],[0-10],[3-10],[0-10];%')


...however this is not returning the desired results i.e. it returns values that contain a 0 in the 3rd part of the comma separated number string.
0
Comment
Question by:drl1
2 Comments
 
LVL 20

Accepted Solution

by:
TheAvenger earned 500 total points
ID: 38796297
The groups for numbers should be like this:

[0-9]

This means all digits from 0 to 9. So your query would be:

select * from mytable where
(myfield like '%foo: [0-9],[0-9],[3-9],[0-9];%') OR
(myfield like '%bar: [0-9],[0-9],[3-9],[0-9];%')

If you have 10 as a number, you need to add more conditions where 10 is specified explicitly
0
 

Author Comment

by:drl1
ID: 38796322
Thanks, I got it working in that manner just before you posted. So any values above 9 have to be coded explicitly and a range can't simply adopt the syntax [0-20] or [0-100]....good to know!

Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question