Solved

SELECT QUERY SYNTAX

Posted on 2013-05-21
6
479 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

808 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