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 2012 Forums
 Transact-SQL (2012)
 SQL Concat Search

Author  Topic 

ehauser0613
Starting Member

6 Posts

Posted - 2013-10-03 : 14:40:42
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-03 : 15:10:38
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
Go to Top of Page

ehauser0613
Starting Member

6 Posts

Posted - 2013-10-03 : 15:27:40
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-03 : 15:37:26
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

6 Posts

Posted - 2013-10-03 : 15:39:33
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

982 Posts

Posted - 2013-10-03 : 21:51:24
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
   

- Advertisement -