Solved

SELECT QUERY SYNTAX

Posted on 2013-05-21
6
484 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 500 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

689 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