Remove Alpha char's but leave in the decimal point

  • 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.

  • 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

  • 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


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply