Solved

SELECT QUERY SYNTAX

Posted on 2013-05-21
6
471 Views
Last Modified: 2013-08-02
hi, lets say i have an table like this:


id
1
2
3
47
21

and i want to select all rows that mach the numbers inside this string:   '23-12-1-5-3-47'

what would be the syntax?
0
Comment
Question by:rafaelrgl
6 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39186132
select id
from tableX
where id IN(23,12,1,5,3,47)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39186139
the above assumes ID is integer values.

You could also achieve the same result using a set of OR conditions. In fact the syntax above is such a brief version of this:
select id
from tableX
where (    ID =23
        OR ID = 12
        OR ID = 1
        OR ID = 5
        OR ID = 3
        OR ID = 47
        )

Open in new window

0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 39186160
I have an string to comparte with the numbers, so first answer does not work:

lets say i have this:

declare @test varchar(20) = '23-12-1-5-3-47'

i can change the - to , but still will be an string

that's my problem, if it was numbers it would be easy.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39186221
Your parameter is a string that just happens to look like a set of integers; but it is still ONE STRING of characters.

if you are evaluating an integer field against this string via IN(), you not only need to convert to int, but you also need to divide the ONE STRING into MANY INT values, hence you must apply a technique such as this::
DECLARE @p1 varchar(max)
DECLARE @Splitter char(1)
SET @Splitter = '-'

SET @p1 = '2-3-4-5'


;WITH
SplitID AS(
  SELECT CAST('<v>' + REPLACE(@p1, @Splitter, '</v><v>') + '</v>' AS XML) AS Split
)
select
*
from YourTable
where (@p1 is null
       or
       ID IN (
                SELECT cast(x.v.value('.', 'bigint') as bigint) AS Value
                FROM SplitID
                CROSS APPLY Split.nodes('//v') x(v)
             )
      )

Open in new window

see this at: http://sqlfiddle.com/#!3/1fa93/7108

This xml technique assumes those parameters will only every carry digits and the delimiter ( - ). i.e. if you put bad characters into the parameters the query would fail and don't use a delimiter that would "disturb XML" like a > or < (not that you are likely to).

I think its also worth mentioning that this xml based approach is also based on the assumption you are not selecting thousands of items, instead I hope you are likely to be selecting a relatively small number of these. Please test against the largest likely selections of these to ensure performance is acceptable.

These are useful references if you need more information or want to explore this parameter issue more deeply:

an often cited reference on this split string topic:
http://www.sommarskog.se/arrays-in-sql.html

useful introduction to the background of this issue, and some alternative methods for handling it
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 39186224
I'd look at doing something like this.....

Have a look at the code here -> http://rextester.com/ROAYWA64678

Now if you change the line in that that reads....
      SET @xml = N'<root><r>' + replace(@IDs,',','</r><r>') + '</r></root>'
to be
      SET @xml = N'<root><r>' + replace(@IDs,'-','</r><r>') + '</r></root>'

That will now parse your string and return a table that contains a single row for each number in your sting....
So you can now use something along the lines of

Select ID from tableX where ID in (Select * from Temp_Table_returned_From _above_Function)

I'll leave you to fill in the gaps as its past my bed time.....
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39378253
Here is a version that will work with your value as a string.

CREATE TABLE #tmp1 (Val1 INT)
INSERT INTO #tmp1(Val1)
VALUES(1),(2),(3),(47),(21)

SELECT t.*
FROM #tmp1 t
WHERE '23-12-1-5-3-47' LIKE '%' + CAST(t.Val1 as VARCHAR) + '%'

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now