Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-14
17
Medium Priority
?
400 Views
Last Modified: 2012-05-11
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?

0
Comment
Question by:jsaacson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +3
17 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 34892631
Is this limited to the second value only?  or do you need a query to get the 3rd, 4th, 5th etc values also?
0
 

Author Comment

by:jsaacson
ID: 34892649
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 34892677
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 Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 33

Expert Comment

by:knightEknight
ID: 34892682
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
 

Author Comment

by:jsaacson
ID: 34892689
I'm going to try this out
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 34892697
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
 
LVL 7

Expert Comment

by:lundnak
ID: 34892714
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
 

Author Comment

by:jsaacson
ID: 34892775

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
 
LVL 41

Expert Comment

by:Sharath
ID: 34893105
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34893363
where charindex('¦',yourcolumn,1)>0
  and substring(yourcolumn,charindex('¦',yourcolumn,1),4) in ('¦10 ','¦10¦')
0
 
LVL 4

Expert Comment

by:Ztinel
ID: 34894598
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 34896738

>> 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
 
LVL 33

Expert Comment

by:knightEknight
ID: 34896745
(the 2 represents the position in the delimited string)
0
 

Author Comment

by:jsaacson
ID: 34909108
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
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 34909159
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35021635
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
 

Author Closing Comment

by:jsaacson
ID: 35022220
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question