?
Solved

SELECT QUERY SYNTAX

Posted on 2013-05-21
6
Medium Priority
?
485 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
[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
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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