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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
6065 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
6065 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
982 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  
 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.05 seconds. Powered By: Snitz Forums 2000