String Extraction - SQL 2012

1

Hi,

I need to make changes to the below to only extract '2002' as the result.

select REPLACE(REPLACE(SUBSTRING('CKnovelty2002_pouch-5AR-OneSize',1,CHARINDEX('-','CKnovelty2002_pouch-5AR-OneSize',1) - 1),'CKnovelty',''),'CKflower','')

At the moment, I am getting '2002_pouch' as the result which is incorrect. The expected output is '2002'.

Can somebody please help in this regard ?

Many thanks.

2

select REPLACE(REPLACE(SUBSTRING('CKnovelty2002_pouch-5AR-OneSize',1,CHARINDEX('_','CKnovelty2002_pouch-5AR-OneSize',1) - 1),'CKnovelty',''),'CKflower','')

3

Hi,

Many thanks for your kind response and it would work well in certain situations. However, there are values which have only '-'.

So I need to add the CHARINDEX for both '_' as well as for '-'. That is the problem I am facing.

Do you know how to add the CHARINDEX for '_' within the existing logic ?

Thanks.

4

can you please share some sample data

5

Yes sure, thanks-

CKnovelty190606-171-OneSize (Expected result - 190606)

CKnovelty2002_bag-5AR-OneSize (Expected result - 2002)

775816-171-OneSize (Expected result - 775816)

841108-6BD-OneSize (Expected result - 841108)

CKnovelty2002_card-5AR-OneSize (Expected result - 2002)

Many thanks.

6 
-- *** Test Data ***
CREATE TABLE #t
(
	TestString varchar(255) NOT NULL
);
INSERT INTO #t
VALUES ('CKnovelty190606-171-OneSize')
	,('CKnovelty2002_bag-5AR-OneSize')
	,('775816-171-OneSize')
	,('841108-6BD-OneSize')
	,('CKnovelty2002_card-5AR-OneSize');
-- *** End Test Data ***

SELECT TestString
	,SUBSTRING(LEFT(TestString, PATINDEX('%[^0-9,A-Z]%', TestString) -1),  PATINDEX('%[0-9]%', TestString), 255) AS Result
FROM #t;
7

Thanks for your very helpful reply. Do you know if PATINDEX can be used in SSIS expression ?