SELECT SQL

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

priyasaravAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
BrandonGalderisiCommented:
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
 
priyasaravAuthor Commented:
This is giving all the Grants ... i want only the top 1 grants for each employee
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
BrandonGalderisiCommented:
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
 
priyasaravAuthor Commented:
I get the below error

Msg 195, Level 15, State 10, Procedure DiscrepancySupplementsGet, Line 73
'row_number' is not a recognized function name.
0
 
BrandonGalderisiCommented:
You aren't using SQL Server 2005 are you?  I best you're on 2000?
0
 
priyasaravAuthor Commented:
Thannks. I have to refine my question which will be in detail.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.