Jack Jack - 3 months ago 10
SQL Question

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

Answer

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