Solved

SELECT SQL

Posted on 2009-04-08
7
177 Views
Last Modified: 2012-05-06
DECLARE @GrantNumber varchar(2000)

set @GrantNumber = '%01NS061975%|%03NS065487%|%37NS024247%|'

SELECT GrantNumber from TableA WHERE

GRANTNUMBER IN ('%01NS061975%','%03NS065487%','%03NS065487%')


I need to split the @GrantNumber delimited by | and use the 'IN' condition with 'LIKE'

Also it should use the wild card card character.

Basically condition where i need to use the wild card with LIKE
DECLARE @GrantNumber varchar(2000)
 
set @GrantNumber = '%01NS061975%|%03NS065487%|%37NS024247%|'
 
SELECT GrantNumber from TableA WHERE
 
GRANTNUMBER IN ('%01NS061975%','%03NS065487%','%03NS065487%')
 
 
I need to split the @GrantNumber delimited by | and use the 'IN' condition with 'LIKE'
 
Also it should use the wild card card character.
 
Basically condition where i need to use the wild card with LIKE

Open in new window

0
Comment
Question by:priyasarav
[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
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097335
you can't "LIKE" and "IN".


You can however use this function to split the value:

http://sqlservernation.com/blogs/howtos/archive/2009/03/07/converting-a-delimited-string-into-a-table.aspx

And join to the results.

SELECT a.GrantNumber from TableA  a
join dbo.fn_DelimitedToTable(@GrantNumber,'|') b
on a.grantnumber like b.theValue
 
0
 

Author Comment

by:priyasarav
ID: 24097652
This is giving all the Grants ... i want only the top 1 grants for each employee
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097697
Ok.  Substitute your "employee" identifier below in place of employee_id.
select * from 
(SELECT employee_id,a.GrantNumber,row_number() over(partition by a.employee_id order by a.grantnumber) rn
from TableA  a
join dbo.fn_DelimitedToTable(@GrantNumber,'|') b
on a.grantnumber like b.theValue) b
where rn=1

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:priyasarav
ID: 24097796
I get the below error

Msg 195, Level 15, State 10, Procedure DiscrepancySupplementsGet, Line 73
'row_number' is not a recognized function name.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097960
You aren't using SQL Server 2005 are you?  I best you're on 2000?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24097993
If you Just want employee_id and grantnumber, you can run this:

SELECT a.employee_id,min(a.GrantNumber) grantnumber
from TableA  a
join dbo.fn_DelimitedToTable(@GrantNumber,'|') b
on a.grantnumber like b.theValue
group by a.employee_id


If you want the entire TableA record:

select tablea.* 
from tablea
join 
  (SELECT employee_id,min(a.GrantNumber) grantnumber
  from TableA  a
  join dbo.fn_DelimitedToTable(@GrantNumber,'|') b
  on a.grantnumber like b.theValue
  group by a.employee_id) b
on tablea.employee_id = b.employee_id
and tablea.grantnumber = b.grantnumber

Open in new window

0
 

Author Closing Comment

by:priyasarav
ID: 31568031
Thannks. I have to refine my question which will be in detail.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

622 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