October 18, 2011 at 2:08 am
Morning all
I have wrote this function to strip out any alpha characters but I need it to leave in a decimal point
ALTER FUNCTION dbo.RemoveNonNumericCharacters (@Temp VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
While PatIndex('%[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
Return @Temp
END
select dbo.RemoveNonNumericCharacters('1.2 SORRY NOW SOLD')
Returns 12, I would like it to return 1.2
I have tried adding [^.] into the pat index at the side of [^0-9] but to no avail, unsure how to give pat index a multiple patern to check.
Any help would be appreciated.
October 18, 2011 at 3:05 am
Check this one.
ALTER FUNCTION dbo.RemoveNonNumericCharacters (@Temp VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
While PatIndex('%[^0-9,.]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9,.]%', @Temp), 1, '')
Return @Temp
END
Shatrughna
October 18, 2011 at 3:14 am
Here's a version without a LOOP.
ALTER FUNCTION dbo.RemoveNonNumericCharacters (@Temp VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
--Use a real tally table for performance, here's one on the fly for testing
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM t4 x, t4 y)
SELECT TOP 1 @Temp = STUFF(@Temp, PATINDEX('%[^0-9,.]%', @Temp), num, '')
FROM tally
WHERE PATINDEX('%[^0-9,.]%', STUFF(@Temp, PATINDEX('%[^0-9,.]%', @Temp), num, '')) = 0
RETURN @Temp
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply