learned/learning/to learn RSS 2.0
# Friday, 01 May 2009

This works in Oracle, hope there is something similar to this in Sql Server too! There might be some other good way to do this, if so, please let me know.

SELECT * FROM TABLE1 WHERE COLUMN1 IN (‘ABC’,’XYZ’,’PQR’) 

Say suppose the strings ‘ABC’,’XYZ’ and ’PQR’ need to be grouped as a single string in the frontend and to be passed as a parameter.  How can we do it? We cannot write like this –

SELECT * FROM TABLE1 WHERE COLUMN1 IN (IN_PARAMTER)

There are many ways to do it:

  1. We can write an inline query.
  2. We can split the strings and group it as a table type.
  3. We can write dynamic sql.

There is one more way to do it and found it to be easy, so thought of sharing.

Here it is:

SELECT * FROM TABLE1 WHERE COLUMN1 IN
(          
 SELECT  REGEXP_SUBSTR(In_Parameter ,'[^,]+',1,LEVEL)        
 FROM    DUAL
 CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (In_Parameter, '[^,]')) + 1
)
 

It is enough if we pass the parameter as ‘ABC,XYZ,PQR’ instead of “‘ABC’,’XYZ’,’PQR’”.

More about Oracle Regular Expressions - http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/functions116.htm

Friday, 01 May 2009 18:33:29 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
Oracle
Comments are closed.
Navigation
Archive
<2017 June>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Gokulnath
Sign In
Statistics
Total Posts: 41
This Year: 0
This Month: 0
This Week: 0
Comments: 47
Themes
Pick a theme:
All Content © 2017, Gokulnath
DasBlog theme 'Business' created by Christoph De Baene (delarou)
The new movement has made a number of changes. First, precise instantaneous jump calendar display tag heuer replica large switching time is scheduled for midnight. The power required for this process will slowly build up within hours. Furthermore, LANGE 1 escapement now available eccentric balance weight balance wheel and homemade free omega uk watch sprung. Means provided in the hand-carved balance wheel splint omega replica underneath, 21,600 vibrations per hour. This table also hublot replica retains the reliable double-barrel, power reserve of 72 hours.