April 24, 2008 at 2:07 pm
I'm passing into a stored procedure from a multi select in Reporting Services:
@sku = '000000610214615336 - Copper,000000610214615343 - Sage'
I need to dissect the string and only get the numbers to use in a where statement
The where statement is such:
where sku = @SKU
The table which stores SKU only like 000000610214615336,000000610214615343, etc
April 24, 2008 at 3:01 pm
CREATE FUNCTION dbo.fnExtractDigitSequence
(
@ColumnDelimiter CHAR(1) = ',',
@Filter VARCHAR(8000) = '[0-9]',
@UserData VARCHAR(8000) = NULL
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE@Position INT,
@ValidChar TINYINT,
@LastValidChar TINYINT
SELECT@Position = LEN(@UserData),
@LastValidChar = 0
WHILE @Position > 0
SELECT@ValidChar =CASE
WHEN SUBSTRING(@UserData, @Position, 1) COLLATE Latin1_General_BIN LIKE @Filter THEN 1
ELSE 0
END,
@UserData =CASE
WHEN @ValidChar = 1 THEN @UserData
WHEN @LastValidChar = 0 THEN STUFF(@UserData, @Position, 1, '')
ELSE STUFF(@UserData, @Position, 1, @ColumnDelimiter)
END,
@LastValidChar = @ValidChar,
@Position = @Position - 1
RETURNCASE
WHEN @UserData LIKE @ColumnDelimiter + '%' THEN SUBSTRING(@UserData, 2, 7999)
ELSE NULLIF(@UserData, '')
END
END
Call with
SELECTdbo.fnExtractDigitSequence(',', '[0-9]', '...,,,,,123456789,,,,,,.........876564532$$££^^(.........,,,,,')
N 56°04'39.16"
E 12°55'05.25"
April 25, 2008 at 12:42 pm
Another alternative might be somethinng like:
declare @aTest table (combinedString varchar(25))
insert into @aTest
select '1015Abc' union all
select '1022' union all
select '5157D2' union all
select 'Db2' union all
select '#' union all
select '#d' union all
select '' union all
select '105#' union all
select '205128 ' union all
select null
--select * from @aTest
select combinedString,
left(combinedString, patindex('%[^0-9]%',combinedString+'X') - 1) as [Numeric Part],
substring(combinedString, patindex('%[^0-9]%',combinedString+'X'),25) as [Remainder]
from @aTest
/* -------- Sample Output: --------
combinedString Numeric Part Remainder
------------------------- ------------------------- -------------------------
1015Abc 1015 Abc
1022 1022
5157D2 5157 D2
Db2 Db2
# #
#d #d
105# 105 #
205128 205128
NULL NULL NULL
*/
Edit:
I asked a similar question in the MSDN Transact SQL forum here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3036691&SiteID=1
The solution that I gave was actually Mark's solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply