Solved

SELECT SQL

Posted on 2009-04-08
7
176 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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