SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is very handy function when working with Alphanumeric String only. I have used this many times.

CREATE FUNCTION dbo.UDF_ParseAlphaChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET
@string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
END
SET
@string = @string
RETURN @string
END
GO

—-Test
SELECT dbo.UDF_ParseAlphaChars('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO

Result Set : ABCID4e5F6

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – 2005 – List all the database
Next Post
SQL SERVER – Query to Find First and Last Day of Current Month – Date Function

Related Posts

86 Comments. Leave new

  • Can you explain why you have these two lines?

    SET @IncorrectCharLoc = PATINDEX(‘%[^0-9A-Za-z]%’, @string) — 2nd one
    SET @string = @string

    I created a version without them and it works fine. I don’t see the point of these lines, but if I am missing something I would like to know. Either way, it is a great solution. Thank you.

    Reply
  • Hi ,

    I am new to sql

    Could some one help me with parsing function in SQL server in splitting the value form comma separated string.

    The output of the column looks like…

    Patient Othernumber
    —————————–
    123
    567,56e3,785
    746
    66y,8968g,5656,4435

    i wan to split this output.

    Thank you.

    Reply
  • Hi,

    Thank you very much for your quick response. I have written a function to split these output and it works fine for me.

    Reply
  • Arturo pliego
    July 16, 2014 4:51 am

    how can i get negatives numbers?

    Reply
  • hi

    i have two tables first table column name have varchar datatype second table have numeric data tyep how i insert the value from second to first table

    Reply
  • Justin Ian Hicarte
    April 1, 2015 8:51 am

    Thanks for this article.

    Reply
  • Hi,
    first of all just want to say i am a newbie in SQL so please bear with me.
    I am hoping you can help me..i want to query the Top 20 percent of software installed on all companies PC (around 15,000 PC’s) and i used the SQL Query below…

    SELECT TOP 20 percent s.DisplayName0, COUNT (*) as Total
    FROM v_Installed_Software_Categorized as s
    group by s.DisplayName0
    ORDER BY Total DESC

    the output shows all the top 20 percent software and also shows the different versions of the software (which is expected)…now since there are a lot of versions of the software for example:

    BMC Remedy Request System 7.5.00 patch 006 Install 1 (Total Count 2000)
    BMC Remedy Request System 7.6.04 SP3 Install 1 (Total count 1500)
    Winzip 9 (Total Count 9000)
    Winzip 10 (Total Count 2000)
    MS Visio Standard 2010 (Total Count 3000)
    MS Visio Standard 2013 (Total Count 1000)

    I am hoping you can help me please in creating a variation of the UDF function where on my query it will remove the versions/numbers/SP1/SP2/SP3 as the objective of the query is only to find out the total numbr of software regardless of version because the reason we want that kind of output is because we want to Virtualize/ThinApp those software so we are not interested on the version as we will use the latest version of the software to virtualize/ThinApp the software but we want to know the rank (Top 20 percent) of the software so we are sure that the software we are going to virtualize is rank Top 20 percent.

    i am hoping at using the following query:

    SELECT TOP 20 percent s.DisplayName0, COUNT (*) as Total
    FROM v_Installed_Software_Categorized as s
    group by dbo.UDF_ParseAlphaChars (s.DisplayName0)
    ORDER BY Total DESC

    but i am hoping you can guide me on the correct syntax for dbo.UDF_ParseAlphaChars so the output will be:

    BMC Remedy Request System (Total Count 3500)
    Winzip (Total Count 11000)
    MS Visio Standard (Total Count 4000)

    of course the s.DisplayName0 is on first column and the Total Count is on the second column.

    Reply

Leave a Reply

Menu