Solved

T SQL search instring

Posted on 2013-01-19
2
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

728 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