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)
86 Comments. Leave new
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.
What is your expected result?
Hi,
Thank you very much for your quick response. I have written a function to split these output and it works fine for me.
how can i get negatives numbers?
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
gunalan, have you tried using convert function?
Thanks for this article.
Justin Ian Hicarte – I am glad that you liked it.
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.