SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.

/* Run the UDF with different test values */SELECT dbo.udf_GetNumeric('') AS 'EmptyString';SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';SELECT dbo.udf_GetNumeric('123456') AS '123456';SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';GO

As result of above script self explainary, I will be not describing it in detail. If you use any alternative method to do the same task, please let me know.

Hi Dave, thanks a lot, it saved us some time. When a long string is passed to the function, the function will iterate char by char from the second occurance of non-numeric char. I made a small improvement, so function is not iterating across a series of non-numeric chars.

I have a product names like this SAN Ports – 6 EMC/HDS Tier 1 864GB,SAN Ports – 8 EMC/HDS Tier 1 1152GB. I need to extract 864 and 1152 for the GB value only.How do I get the values.I have used getnumeric functions(using PATINDEX) but it returns 1684 and 811152 like this.Please help me to solve the issue.its very urgent

Hi Pinal – I have always gotten help from your post but this the first time I am posting a question. Please help.

1) I have a column which has pharmacy name, street Address, city, State, Zip code. I need to put address in separate fields. Field starts with Pharmacy name and then hyphen’-‘ and the address in different order like sometimes it has city and state after hyphen and then street address and other times its street address following hyphen. I am able to get just the pharmacy name just looking for the first hyphen but it’s challenging to get the street address which is starting with a number.

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.