Extract part of a string with inconsistent length

Curlywirly · February 10, 2025, 6:30am

Hi All,

I want to extract the size dimension from the SKU fields which comes in different formats. I tried SPLIT_PART and CHARINDEX/Substr but not able to get it to work for all different formats.

Example SKU formats -

Expected Output

2XL
S-M
1

Note- The letters for colour is not consistently 3 letter. Same for Style. Only the format is static - SKU|XX|XXXX and so on.

Database - Snowflake

Quick response is greatly appreciated!

Ifor · February 10, 2025, 9:02am

As this is a SQL Server site, I doubt many know snowflake.
This is just basic string handling. With t-sql:

/* *** Test Data *** */
CREATE TABLE #t
(
	YourCol varchar(500) NOT NULL
	PRIMARY KEY
);
INSERT INTO #t
VALUES ('C11544-BLK-2XL|AC|PQRS|Baseball Cap')
	,('C11544-BLK-S-M|AC|PQRS|Baseball Cap')
	,('K45602-NA-1|XZ|XYSUA|Pant');
/* *** End Test Data *** */

SELECT SUBSTRING(X2.SecondBit, CHARINDEX('-', X2.SecondBit) + 1, 255) As Result
FROM #t T
	CROSS APPLY (VALUES(LEFT(T.YourCol, CHARINDEX('|', T.YourCol) - 1))) X1 (FirstBit)
	CROSS APPLY (VALUES(SUBSTRING(X1.FirstBit, CHARINDEX('-', X1.FirstBit) + 1, 255))) X2 (SecondBit);

harishgg1 · July 30, 2025, 9:12am

hi

hope this helps

create data table script

drop table if exists #Temp 

create table #temp (col1 varchar(200))

insert into #Temp 
select '1.C11544-BLK-2XL|AC|PQRS|Baseball Cap'
union all
select '2.C11544-BLK-S-M|AC|PQRS|Baseball Cap'
union all
select '3. K45602-NA-1|XZ|XYSUA|Pant'

SELECT  
   right(value,charindex('-',reverse(value))-1)
FROM     
  #Temp CROSS APPLY string_split(col1,'|',1)
WHERE 
  ordinal = 1