SQL Concat Search - 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 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

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

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

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!

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources