SQL Like with comma seperated - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

SQL Like with comma seperated

Author

Topic

k80sg
Starting Member

7 Posts

Posted - 2011-11-28 : 02:39:39

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 03:25:16

try like

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

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

k80sg
Starting Member

7 Posts

Posted - 2011-11-28 : 03:46:58

I tried but that didnt fetch me the desired results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 04:48:26

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources