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 2012 Forums
 Transact-SQL (2012)
 SQL Concat Search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ehauser0613
Starting Member

USA
6 Posts

Posted - 10/03/2013 :  14:40:42  Show Profile  Reply with Quote
Hello,

I am trying to find a way to have query search through the text of a concatenated string and see if either side of the string matches a variable (something that is chosen (concatenation delimited by a ' | '.

Example:

User chooses: Apple

Concatenated column has values like (not including the text in the quotes):

Apple | Apple "Both match"
Apple | Orange "Left side matches"
Pear | Apple "Right side matches"
Pear | Plum "Neither side matches"

Using this example, I am trying to write a case statement that evaluates the text on either side of the pipe and then returns the result (the parts in quotes) based on what it finds.

I really just need help to figure out how to search for an instance of a variable on either side of a pipe | in a concatenated string.

If you have any questions, please let me know.

Thanks,

Erik

TG
Flowing Fount of Yak Knowledge

USA
5948 Posts

Posted - 10/03/2013 :  15:10:38  Show Profile  Reply with Quote
try this:
where ' | ' + col + ' | ' like '% | ' + @Choice + ' | %'

EDIT:


declare @choice varchar(25)
set @choice = 'Apple'
select col
from   (
       select col = 'Apple | Apple' union all
       select 'Apple | Orange' union all
       select 'Pear | Apple' union all
       select 'Pear | Plum'
       ) d 
where ' | ' + col + ' | ' like '% | ' + @Choice + ' | %'

OUTPUT:
col
--------------
Apple | Apple
Apple | Orange
Pear | Apple


Be One with the Optimizer
TG

Edited by - TG on 10/03/2013 15:12:22
Go to Top of Page

ehauser0613
Starting Member

USA
6 Posts

Posted - 10/03/2013 :  15:27:40  Show Profile  Reply with Quote
Thank you TG!

This is very helpful! Now I just need to work on a case statement that returns a result depending on which side of the concatenated column the users choice is.

Thanks,

Erik
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5948 Posts

Posted - 10/03/2013 :  15:37:26  Show Profile  Reply with Quote
You're welcome. Is there always only one "|"? ie: just two values, left and right?
If so then maybe this added to the SELECT:

       ,case 
        when left(col, len(@choice)) = @choice then 'Left'
        when right(col, len(@choice)) = @choice then 'Right'
        else 'no match'
        end as [side]



Be One with the Optimizer
TG
Go to Top of Page

ehauser0613
Starting Member

USA
6 Posts

Posted - 10/03/2013 :  15:39:33  Show Profile  Reply with Quote
With the way that the column is concatenated there is always a | in between the two values. I will work with what you just gave me and see if I can get it working.

Thanks!
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
947 Posts

Posted - 10/03/2013 :  21:51:24  Show Profile  Reply with Quote
this will work better if the variable is not a whole word

, case when CHARINDEX(@choice, col) < CHARINDEX('|', col) then 'Left' when CHARINDEX(@choice, col) > CHARINDEX('|', col) then 'Right' else 'no match' end as [side]
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.5 seconds. Powered By: Snitz Forums 2000