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
Technology Tips and News
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
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)
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. :)
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
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.
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 @tablePerhaps if you posted example data and DDL we could better understand your requirement..