Solved

SELECT SQL

Posted on 2009-04-08
7
172 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
index  - last use and update 8 56
Not selecting duplicate data 6 42
Table create permissions on SQL Server 2005 9 40
Restrict result set 1 33
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
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…

947 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

21 Experts available now in Live!

Get 1:1 Help Now