March 10, 2020 at 11:25 am
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.
March 10, 2020 at 11:38 am
select REPLACE(REPLACE(SUBSTRING('CKnovelty2002_pouch-5AR-OneSize',1,CHARINDEX('_','CKnovelty2002_pouch-5AR-OneSize',1) - 1),'CKnovelty',''),'CKflower','')Your CHARINDEX was looking for '-' rather than '_'.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 10, 2020 at 11:42 am
select REPLACE(REPLACE(SUBSTRING('CKnovelty2002_pouch-5AR-OneSize',1,CHARINDEX('_','CKnovelty2002_pouch-5AR-OneSize',1) - 1),'CKnovelty',''),'CKflower','')Your CHARINDEX was looking for '-' rather than '_'.
Hi Neil,
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.
March 10, 2020 at 12:14 pm
declare
@txt varchar(100)='CKnovelty2002_pouch-5AR-OneSize';
--@txt varchar(100)='CKflower2002_pouch-5AR-OneSize';
select iif(left(@txt, 9)='CKnovelty', substring(@txt, 10, 4), substring(@txt, 9, 4));
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 10, 2020 at 12:27 pm
DECLARE
@String VARCHAR(100) = 'CKflower2002-pouch-5AR-OneSize'
select
SUBSTRING(@string,PATINDEX('%[0-9]%',@string),PATINDEX('%[^0-9a-z]%',@string)-PATINDEX('%[0-9]%',@string))
That will work for whatever the first delimiter is and for any length of string before the numbers. It does assume that the numbers you want will always come after the set of letters.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 10, 2020 at 12:44 pm
DECLARE
@String VARCHAR(100) = 'CKflower2002-pouch-5AR-OneSize'
select
SUBSTRING(@string,PATINDEX('%[0-9]%',@string),PATINDEX('%[^0-9a-z]%',@string)-PATINDEX('%[0-9]%',@string))That will work for whatever the first delimiter is and for any length of string before the numbers. It does assume that the numbers you want will always come after the set of letters.
Thanks again, the thing I forgot to mention was that I need use this within the SSIS expression and it does not support PATINDEX.
Do you know if there is any substitute for PATINDEX in SSIS ? Thanks.
March 10, 2020 at 12:52 pm
Neil Burton wrote:DECLARE
@String VARCHAR(100) = 'CKflower2002-pouch-5AR-OneSize'
select
SUBSTRING(@string,PATINDEX('%[0-9]%',@string),PATINDEX('%[^0-9a-z]%',@string)-PATINDEX('%[0-9]%',@string))That will work for whatever the first delimiter is and for any length of string before the numbers. It does assume that the numbers you want will always come after the set of letters.
Thanks again, the thing I forgot to mention was that I need use this within the SSIS expression and it does not support PATINDEX.
Do you know if there is any substitute for PATINDEX in SSIS ? Thanks.
There's a function called FINDSTRING which I found by using my favourite search engine. I'm not sure if it takes wildcards like T-SQL does but I'm sure you could do a bit of testing based on what's been provided above.
It really does help if you provide all the information upfront. Your question only mentioned one delimiter, your next post raised the point that there might be two and only now do you mention that this is in SSIS. You'd probably have got a working solution much more quickly if you'd mentioned all of this in the first place.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
March 10, 2020 at 1:15 pm
pwalter83 wrote:Neil Burton wrote:DECLARE
@String VARCHAR(100) = 'CKflower2002-pouch-5AR-OneSize'
select
SUBSTRING(@string,PATINDEX('%[0-9]%',@string),PATINDEX('%[^0-9a-z]%',@string)-PATINDEX('%[0-9]%',@string))That will work for whatever the first delimiter is and for any length of string before the numbers. It does assume that the numbers you want will always come after the set of letters.
Thanks again, the thing I forgot to mention was that I need use this within the SSIS expression and it does not support PATINDEX.
Do you know if there is any substitute for PATINDEX in SSIS ? Thanks.
There's a function called FINDSTRING which I found by using my favourite search engine. I'm not sure if it takes wildcards like T-SQL does but I'm sure you could do a bit of testing based on what's been provided above.
It really does help if you provide all the information upfront. Your question only mentioned one delimiter, your next post raised the point that there might be two and only now do you mention that this is in SSIS. You'd probably have got a working solution much more quickly if you'd mentioned all of this in the first place.
FINDSTRING does not take wildcard characters so will need to test other options. Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply