Solved

SELECT SQL

Posted on 2009-04-08
7
171 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
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
This is giving all the Grants ... i want only the top 1 grants for each employee
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:priyasarav
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thannks. I have to refine my question which will be in detail.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now