• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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?

0
jsaacson
Asked:
jsaacson
  • 7
  • 5
  • 2
  • +3
1 Solution
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now