How to return partial string using CharIndex and Left in same select...

Select

left(

[Description],(charindex(';',[Description],1)-1))

from xxxx

Example of Descrption contains

Ankle Supports; Color=Black; Size=S

So I want the left side up to and NOT including the semi colon

TIA

June 22nd, 2015 4:02pm

try this

declare @check varchar(max)

set @check='Ankle Supports; Color=Black; Size=S'

select Substring(@Check,1,charindex(';',@check,1)-1)
--OR---
select left(@check,charindex(';',@check,1)-1)

Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2015 4:09pm

You can use a function to parse the string similar to this:

ALTER FUNCTION [dbo].[fn_SplitValues]
(
	@ColumnData varchar(MAX),
	@SplitOn varchar(5)
)  
RETURNS @RtnValue table 
(Data varchar(255)) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@ColumnData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@ColumnData,1,Charindex(@SplitOn,@ColumnData)-1)))

		Set @ColumnData = Substring(@ColumnData,Charindex(@SplitOn,@ColumnData)+LEN(@SplitOn),len(@ColumnData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@ColumnData))

	Return
END


Then you could split as many values as you wanted like this:

declare @tab table (id int, list varchar(max))
insert into @tab (id, list) values (1, 'Ankle Supports;Color=Black;Size=S'), (2, 'Ankle Supports;Color=Black;Size=M'), (3, 'Ankle Supports;Color=Black;Size=L')
select id, data
from @tab
cross apply dbo.fn_SplitValues(list, ';') s

You may also want to put everything into a single row like this:

select id, 
	MAX(CASE WHEN data not like 'Color=%' and data not like 'Size=%' then data end) as Type,
	MAX(CASE WHEN data like 'Color=%' then replace(data, 'Color=', '') end) as Color,
	MAX(CASE WHEN data like 'Size=%' then replace(data, 'Size=', '') end) as Size
from @tab
cross apply dbo.fn_SplitValues(list, ';') s
group by id

This would return your data like:

id	Type	Color	Size
1	Ankle Supports	Black	S
2	Ankle Supports	Black	M
3	Ankle Supports	Black	L
4	Socks	Black	S
5	Ankle Supports	Brown	M
6	Ankle Supports	White	L

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)



June 22nd, 2015 4:23pm

You can take it one step futher, like this:

DECLARE @table TABLE (product VARCHAR(MAX))
INSERT INTO @table (product) VALUES 
('Ankle Supports; Color=Black; Size=S'),
('Frilly Knickers; Color=red; Size=XS')

;WITH base AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS seq, *
  FROM @table
)

SELECT seq, MAX([desc]) AS [desc], MAX(size) AS size, MAX(color) AS color
  FROM (
        SELECT seq, LTRIM(LEFT(value, CHARINDEX('=',value)-1)) AS col, RIGHT(value, LEN(value)-CHARINDEX('=',value))  AS val
          FROM base
            CROSS APPLY toolbox.dbo.splitter('Desc='+product,';') s
       ) s
    PIVOT (
	         MAX(val) FOR col IN ([Desc],[Size],[Color])
			) p
 GROUP BY seq

It does require my splitter function though:

CREATE FUNCTION dbo.splitter(@string VARCHAR(MAX), @delim CHAR(1))
RETURNS @result TABLE (id INT IDENTITY, value VARCHAR(MAX))
AS
BEGIN
 WHILE CHARINDEX(@delim,@string) > 0
  BEGIN
   INSERT INTO @result (value) VALUES (LEFT(@string,CHARINDEX(@delim,@string)-1))
   SET @string = RIGHT(@string,LEN(@string)-CHARINDEX(@delim,@string))
  END
   INSERT INTO @result (value) VALUES (@string)

RETURN
END

Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2015 4:49pm

your code is so far over my head I have no idea what any of it does, I just want to get a the left side of my Description column, there are 2 tables not just random characters, one has the description with the semicolons the other does not, there is no uniqueID to tie the tables together just the descriptions.

 
June 22nd, 2015 5:03pm

But the code you posted already does that:

DECLARE @table TABLE (description VARCHAR(MAX))
INSERT INTO @table (description) VALUES 
('Ankle Supports; Color=Black; Size=S'),
('Frilly Knickers; Color=red; Size=XS')

SELECT LEFT(Description,(CHARINDEX(';',Description,1)-1))
  FROM @table
Perhaps if you posted example data and DDL we could better understand your requirement..
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2015 5:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics