T-SQL: querying nvarchar column with pipe delimited data

hi, we have nvarchar column containing data like this:

aaaa|bbbb|cccc|dddd|.....

What is the best way to get 'cccc' value (third value) ?

Thanks.
quasar_eeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Louis01Connect With a Mentor Commented:
DECLARE @CSV_String varchar(50) = 'aaaa|bbbb|cccc|dddd';
select dbo.fn_GetNthValue(@CSV_String, '|', 3);

Open in new window

Function DROP AND CREATE:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetNthValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetNthValue]
GO

CREATE FUNCTION [dbo].[fn_GetNthValue]
  (
   @string VARCHAR(MAX)
 , @delimiter CHAR(1)
 , @Position int
  )
RETURNS VARCHAR(max)
  BEGIN
	DECLARE @OutputValue varchar(max);
    DECLARE
      @start INT
    , @end INT
    SELECT
      @start = 1
    , @end = CHARINDEX(@delimiter, @string)
    DECLARE @Counter int = 0;
    WHILE @start < LEN(@string) + 1 
      BEGIN
		SET @Counter = @Counter+1;
        IF @end = 0 
          SET @end = LEN(@string) + 1

		IF @Counter = @Position
			SET @OutputValue = SUBSTRING(@string, @start, @end - @start)
			
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
      END
    RETURN @OutputValue
  END
GO

Open in new window

0
 
chaauCommented:
If it is a always a third element you are after you can use CHARINDEX a couple of times, like this:

DECLARE @test varchar(100)
SET @test = 'aaaa|bbbb|cccc|dddd|'
SELECT CASE WHEN CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) > 0
 THEN 
   CASE WHEN CHARINDEX('|', @test, CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1) > 0
   THEN
     SUBSTRING(@test, CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1, CHARINDEX('|', @test, CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1) - CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) - 1)
   ELSE
     SUBSTRING(@test, CHARINDEX('|', @test, CHARINDEX('|', @test) + 1) + 1, 1000)
   END
 END

Open in new window

0
 
Louis01Commented:
p.s. In my function above, line 27 & 28 should really be:
IF @Counter = @Position
BEGIN
    SET @OutputValue = SUBSTRING(@string, @start, @end - @start)
    BREAK
END
0
 
PaulCommented:
>>What is the best way to get 'cccc' value
to store it as a field in a normalized record structure (not as part of a string)

but I realize that's not the answer you are seeking
0
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.

All Courses

From novice to tech pro — start learning today.