I am hoping someone can help me with an example of a user function that will
return just the letters of a string. For example, sending 1RMB23 will return
RMB. Thank you.CREATE FUNCTION ReturnNonNumeric (@.Source varchar(100))
RETURNS varchar(100)
AS
BEGIN
DECLARE @.ReturnNonNumeric varchar(100)
SET @.ReturnNonNumeric = REPLACE(@.Source, '0', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '1', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '2', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '3', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '4', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '5', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '6', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '7', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '8', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '9', '')
RETURN (@.ReturnNonNumeric)
END
--kludgy, but will do what you want. Stu|||quickly wrote this (just for fun)
CREATE FUNCTION [dbo].[udf_AlphaOnly] (@.String varchar(255))
RETURNS varchar(255)
AS
BEGIN
Declare @.nPos Int,@.Strip varchar(150)
Set @.nPos=1
Set @.Strip='0123456789!@.#$%^&*()-_=+[]{}\|;:"<>,./?'
While @.nPos<=Len(@.Strip)
Begin
Set @.String=Replace(@.String,Substring(@.Strip
,@.nPos,1),'')
Set @.nPos=@.nPos+1
End
Return @.String
END|||Wow; that's nice.
<golf clap>
Stu|||Thanks, but if I thunk'd a moment longer, I would have trapped the single
quotes.
Return Replace(@.String,'','')|||Elegant, thank you. And thanks to Stu. I learned from yours also.
"John Cappelletti" wrote:
> quickly wrote this (just for fun)
> CREATE FUNCTION [dbo].[udf_AlphaOnly] (@.String varchar(255))
> RETURNS varchar(255)
> AS
> BEGIN
> Declare @.nPos Int,@.Strip varchar(150)
> Set @.nPos=1
> Set @.Strip='0123456789!@.#$%^&*()-_=+[]{}\|;:"<>,./?'
> While @.nPos<=Len(@.Strip)
> Begin
> Set @.String=Replace(@.String,Substring(@.Strip
,@.nPos,1),'')
> Set @.nPos=@.nPos+1
> End
> Return @.String
> END
>|||Sorry, I was clear until your "moment longer." You've got all single quotes.
You're trapping a string of 5 single quotes and replacing with two single
quotes? Where does that come from?
"John Cappelletti" wrote:
> Thanks, but if I thunk'd a moment longer, I would have trapped the single
> quotes.
> Return Replace(@.String,'','')
>|||I omitted the single quote from the strip string (and space).
Since I grab only 1 character at a time from the strip string, one final
replace is required, and the only way to put a single quote within single
quotes is to double them up.
The 5 should be 4 (I'm distracted by a bad movie)
Return Replace(@.String,'''','')
Good catch!
John
"richardb" wrote:
> Sorry, I was clear until your "moment longer." You've got all single quote
s.
> You're trapping a string of 5 single quotes and replacing with two single
> quotes? Where does that come from?
> "John Cappelletti" wrote:
>
No comments:
Post a Comment