Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SELECT QUERY SYNTAX

Posted on 2013-05-21
6
Medium Priority
?
492 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 49

Accepted Solution

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

579 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