Solved

SELECT SQL

Posted on 2009-04-08
7
173 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

832 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