SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 SQL Like with comma seperated
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

k80sg
Starting Member

7 Posts

Posted - 11/28/2011 :  02:39:39  Show Profile  Reply with Quote
I have a SP query like this:

FROM [Transact] T
LEFT JOIN [Outlet] O
On (T.Outlet_Code = O.Code)
LEFT JOIN [SystemCode] SC
on (CONVERT(NVARCHAR,T.Mode) = SC.Code)

Where (CardNo In (Select [CardNo] from [Card] where [CardNo] = @CardNo and [DeletedBy] is null and [DeletedOn] is null and [MemberID] = @MemberId))

and ((T.TransactDate Between @TransactDateFrom And @TransactDateTo
or @TransactDateFrom is null or @TransactDateTo is null)
and (T.TransactDate >= @TransactDateFrom or @TransactDateFrom is null)

and ((',' + @ReceiptNo +',' LIKE '%,' + T.ReceiptNo + ',%') or @ReceiptNo is null)

Group by T.AutoID, TransactDate,TransactTime, SC.Name, O.Name, ReceiptNo, AmountSpent, TransactPoints, VoidOn

Basically I have a few receipts records in my database, 2 of them are 'sun29842' and 'wis87364'

From this line:

and ((',' + @ReceiptNo +',' LIKE '%,' + T.ReceiptNo + ',%') or @ReceiptNo is null)

I manage to retrieve both 'sun29842' and 'wis87364' records if my searchstring which is passed in with @ReceiptNo is 'sun29842,wis87364' or just 1 of them if the searchstring is 'sun29842'. It returns no records if it's not the exact string for e.g partial string: 'sun298,wis8736' will return nothing which I would also need to display. Please kindly advice. Thanks.

Edited by - k80sg on 11/28/2011 02:41:20

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2011 :  03:25:16  Show Profile  Reply with Quote
try like

and (( @ReceiptNo LIKE '%' + T.ReceiptNo + '%') or @ReceiptNo is null)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

k80sg
Starting Member

7 Posts

Posted - 11/28/2011 :  03:46:58  Show Profile  Reply with Quote
I tried but that didnt fetch me the desired results.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2011 :  04:48:26  Show Profile  Reply with Quote
then use a string parsing function and do like

....
INNER JOIN dbo.ParseValues(@ReceiptNo,',') f
ON T.ReceiptNo  LIKE '%' + f.Val + '%'
...


ParseValues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000