What’s that picture?

In case you were wondering what the picture is in the current banner on this blog, that’s me on a hike in a semi-remote region of Western India (Maharashtra).  Outside of Mumbai, there is a hilltop community named Matheran that has many hiking trails with stunning views of the surrounding area.  Where I am standing [...]

Non-Numeric Characters in SQL – Follow up

Although my previous post was a bit hasty, I wanted to follow up with some additional comments about the magic in the middle of the UDF created to remove any non-numeric characters from a column in a query result set.  As you can see there is a relatively cryptic body of the function that somewhat [...]

SQL Server String Manipulation – Removing Non-Numeric Characters

I recently had a need to get a distinct list of numbers from a freeform text field in a database.  After some analysis, my needs were met by the following approach:
CREATE FUNCTION dbo.UFNG_ONLY_DIGITS (@StrVal AS VARCHAR(max))
RETURNS VARCHAR(max)
AS
BEGIN
      WHILE PATINDEX(‘%[^0-9]%’, @StrVal) > 0
            SET @StrVal = REPLACE(@StrVal,
                SUBSTRING(@StrVal,PATINDEX(‘%[^0-9]%’, @StrVal),1),”)
      RETURN @StrVal
END
The function can then be used [...]