How to search a field containing multiple values separated by a bar |

I have a table that contains a field with multiple values separated by a bar |.
For example:
1584|10|9|XXX|40|-1
1234|100|25|XXX|10|23
432|1006|10|XXX|12|10
10|2010|12|XXX|10|-1

I need to select on the 2nd value.
I have tried using the LIKE command with %|value|% = 10
This picks up any value that matches 10, no matter what position te 10 is in.
I tried using the LIKE command with %|value_|% = 10
This still picks up any match with a 10.

Is there a way to create a select command that would only pick up a match on the 2nd value?

jsaacsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

knightEknightCommented:
Is this limited to the second value only?  or do you need a query to get the 3rd, 4th, 5th etc values also?
0
jsaacsonAuthor Commented:
I do need to be able to select the 3rd, 4th, etc.
I have other fields that allow me to limit my query to one of the fields, so I limit my query to just one of the fields at a time
0
knightEknightCommented:
You can do it by using these two functions:


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- Returns a single-column table from a delimited string
CREATE  FUNCTION [dbo].[csv1] (@Source VARCHAR(MAX), @rowDelim VARCHAR(max)=',') 
	RETURNS @Result TABLE (ID int identity, col1 varchar(MAX)) 
AS
BEGIN
	IF isNull(@rowDelim,char(10))=char(10) 
	begin
		select @rowDelim = char(13)+char(10)
	end
	ELSE
	begin
		select @Source = replace( @Source, @rowDelim+char(13), @rowDelim )
		select @Source = replace( @Source, @rowDelim+char(10), @rowDelim )
	end

	select @Source = rtrim(ltrim(@Source))

	DECLARE @rowDelimPos INT, @StartShift INT, @LenSource INT 
	SET @LenSource = LEN(@Source) 
	SET @StartShift = 1 
	IF RIGHT(@Source, 1) <> @rowDelim 
		SET @Source = @Source + @rowDelim 
	WHILE @StartShift <= @LenSource 
	BEGIN 
		SET @rowDelimPos = CHARINDEX( @rowDelim, @Source, @StartShift )
		INSERT INTO @Result VALUES ( (SUBSTRING(@Source, @StartShift, @rowDelimPos-@StartShift) ))
		SET @StartShift = @rowDelimPos + len(@rowDelim) 
	END
	RETURN
END




CREATE  FUNCTION [dbo].[getDelimitedValue](@Source VARCHAR(MAX), @delim varchar(MAX)=',', @position int=1) 
	RETURNS varchar(MAX)
AS 
BEGIN
	DECLARE @result VARCHAR(MAX)
	select @result = col1
	from  dbo.csv1(@Source,@delim)
	where id = @position
	RETURN @result
END

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

knightEknightCommented:
Then use them like this:


select * from dbo.csv1('1584|10|9|XXX|40|-1','|')

select dbo.getDelimitedValue( '1584|10|9|XXX|40|-1','|', 2 )

0
jsaacsonAuthor Commented:
I'm going to try this out
0
knightEknightCommented:
of course, you would use the column name instead of the hard coded string values:

select dbo.getDelimitedValue( theColumn, '|', 2 ) as SecondPositionValue
from mytable

This function depends on the first, however, so you will need to create both.
0
lundnakCommented:
I don't like this option because it will perform a full scan.
But here it is
where substring(value,charindex('|',value)+1,4000) like '10|%'
0
jsaacsonAuthor Commented:

KnightENight

How do I include this function in a query to select records?
i.e.
select * from audit where tkeyfield is = '10'

Would I say
select audit,*, dbo.csv1(tkeyfield) from audit where dbo.getdelimitedvalue(tkeyfield) = 10

0
SharathData EngineerCommented:
Create a function like this.
CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter char(1) = ' ')
RETURNS @Strings TABLE (position int IDENTITY PRIMARY KEY, value nvarchar(max)) AS 
  BEGIN 
DECLARE @index int
    SET @index = -1
  WHILE (LEN(@text) > 0)
  BEGIN
    SET @index = CHARINDEX(@delimiter , @text)
     IF (@index = 0) AND (LEN(@text) > 0)
  BEGIN 
 INSERT INTO @Strings VALUES (@text)
  BREAK 
    END
     IF (@index > 1)
  BEGIN
 INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END 
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
 RETURN
    END
 GO

Open in new window

You can use this function in your code like this.
select * 
  from @table 
 cross apply dbo.fn_Split(field,'|')
 where position = 2 -- remove this filter if you want to search in all positions
   and value like '%10%'

Open in new window

Here is the output with your sample data.
declare @table table(field varchar(100))
insert @table values ('1584|10|9|XXX|40|-1')
insert @table values ('1234|100|25|XXX|10|23')
insert @table values ('432|1006|10|XXX|12|10')
insert @table values ('10|2010|12|XXX|10|-1')
select * 
  from @table 
 cross apply dbo.fn_Split(field,'|')
 where position = 2 -- remove this filter if you want to search in all positions
   and value like '%10%'
/*
field	position	value
1584|10|9|XXX|40|-1	2	10
1234|100|25|XXX|10|23	2	100
432|1006|10|XXX|12|10	2	1006
10|2010|12|XXX|10|-1	2	2010
*/

Open in new window

0
LowfatspreadCommented:
where charindex('¦',yourcolumn,1)>0
  and substring(yourcolumn,charindex('¦',yourcolumn,1),4) in ('¦10 ','¦10¦')
0
ZtinelCommented:
hi, try this to get the second row:
SELECT SUBSTRING(columnname,
            CHARINDEX('|', columnname) + 1,
            CHARINDEX('|', SUBSTRING(columnname,
            CHARINDEX('|', columnname) + 1,
            LEN(columnname)))-1) FROM tablename
0
knightEknightCommented:

>> How do I include this function in a query to select records?
>> select * from audit where tkeyfield is = '10'


select *
from audit
where dbo.getDelimitedValue( tkeyfield , '|', 2 ) = '10'
0
knightEknightCommented:
(the 2 represents the position in the delimited string)
0
jsaacsonAuthor Commented:
Thank you all for your ideas and suggestions.  As I have some control over the design of the system I am working with, I have elected to modify the table to set up separate fields for each of the values that are included in the existing field separated by the | code.  

However, I have learned a lot from your suggestions and if I chose a solution I would chose KnightEKnight's solution.

Again, thank you all very much for your help

Joel
0
knightEknightCommented:
Separate columns for each field will certainly perform better because you can index them separately as required.  Using any of the string-parsing methods described above, a full table scan would be required for each query of this type.  For small tables or infrequent queries this would not be a problem, but you are right to design it the right way if it is up to you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
A solution has been provided for the question asked...

just because the solution is no longer required by the asker , that no reason to not award points....

0
jsaacsonAuthor Commented:
As the expert agreed, this solution works, but for very large tables it would require reading the entire table each time.  The table I am working with is well over 1 million rows at this time and will continue to grow.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.