Geeks With Blogs
Ben Adler's Development Posting solutions as I find them

---These functions will strip away unwanted characters from a string, leaving only numeric, alpha, or alpha numeric values

create function dbo.NumericOnly(@string varchar(max))

returns varchar(max)

begin

   While PatIndex('%[^0-9]%', @string) > 0

        Set @string = Stuff(@string, PatIndex('%[^0-9]%', @string), 1, '')

return @string

end

 

GO --------------------------------

 

create function dbo.AlphaOnly(@string varchar(max))

returns varchar(max)

begin

   While PatIndex('%[^a-z]%', @string) > 0

        Set @string = Stuff(@string, PatIndex('%[^a-z]%', @string), 1, '')

return @string

end

 

GO --------------------------------

 

create function dbo.AlphaNumericOnly(@string varchar(max))

returns varchar(max)

begin

   While PatIndex('%[^a-z0-9]%', @string) > 0

        Set @string = Stuff(@string, PatIndex('%[^a-z0-9]%', @string), 1, '')

return @string

end

 

GO --------------------------------

 

select

    dbo.NumericOnly('123ABC!!!') as [NumericOnly],

    dbo.AlphaOnly('123ABC!!!') as [AlphaOnly],

    dbo.AlphaNumericOnly('123ABC!@#') as [AlphaNumericOnly]

Posted on Thursday, April 21, 2011 4:18 PM SQL | Back to top


Comments on this post: SQL Functions for Numeric Only, Alpha Only, and Alpha Numeric Only Characters

# re: SQL Functions for Numeric Only, Alpha Only, and Alpha Numeric Only Characters
Requesting Gravatar...
Thank you. These were exactly what I was looking for. --Rob
Left by Rob on Jul 22, 2014 11:19 AM

# re: SQL Functions for Numeric Only, Alpha Only, and Alpha Numeric Only Characters
Requesting Gravatar...
I am working on a project and there I need date code for example if it is 2017/10/11 then the code should appear 7XB(7 is year 17 and X is month oct and B is day 11) and this is connected with part number if part number is same then 7XB should increment by 1 like 7XB1. I have all the months and day code but don’t know how to use it to generate these code and sequence in SQL server and also I can save in database. is there any way to write a query in sql which can decode the date if part number match with the given date.

And the same thing I need to do in VB.net creating a windows application.In that when I give the part number and date it should decode the date and print the label and save the decoded date and sequence in the database.

Please help me I am new to sql
Left by Megha on Oct 12, 2017 9:20 AM

Your comment:
 (will show your gravatar)


Copyright © BenAdler | Powered by: GeeksWithBlogs.net