Solved

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

Posted on 2011-02-14
17
392 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 40

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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now