Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

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

0
priyasarav
Asked:
priyasarav
  • 4
  • 3
1 Solution
 
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
BrandonGalderisiCommented:
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
 
priyasaravAuthor Commented:
Thannks. I have to refine my question which will be in detail.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now