CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user6601036
user6601036

Reputation:

SQL Server UDF Function needs to remove the numbers after the decimal

I am using the following Function to remove extra non numeric characters from the column. 

ALTER Function [dbo].[fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

but if the value is 99.35, it is making it 9935, but i want it to be 99

Can anyone guide me what i am missing in the regex. and i am not sure if it covering up the spaces too or not

Upvotes: 0

Views: 107

Answers (3)

Matt
Matt

Reputation: 14361

If you are not worried there will be multiple decimal points/periods. You can combine a few of the comments and John's answer like so:

ALTER Function [dbo].[fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS INT
AS
BEGIN
    WHILE PATINDEX('%[^0-9.]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END

    RETURN CAST(FLOOR(CAST(@strtext AS NUMERIC)) AS INT)
END

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82000

Declare @String varchar(1000) = '99.35'
Select floor(@String)

Returns 99

Upvotes: 0

S3S
S3S

Reputation: 25142

Instead of just replacing the non-numeric character that is going to be a . or a , or some other delimiter for numeric, just get the left from that point.

SET @strText = LEFT(@strText,PATINDEX('%[^0-9]%', @strText) - 1)

EXAMPLES

DECLARE @strText VARCHAR(1000)

SET @strText = '98,255465465'
SET @strText = LEFT(@strText,PATINDEX('%[^0-9]%', @strText) - 1)
SELECT @strText --98

SET @strText = '97.2554,65465'
SET @strText = LEFT(@strText,PATINDEX('%[^0-9]%', @strText) - 1)
SELECT @strText --97

SET @strText = '982/554.65465'
SET @strText = LEFT(@strText,PATINDEX('%[^0-9]%', @strText) - 1)
SELECT @strText --982

Upvotes: 0

Related Questions