UDF to remove text from mixed string in SQL


 

CREATE function [dbo].[fn_KeepNum](@mixedNumStr varchar(8000))

returns varchar(8000) as

BEGIN

BEGIN

WHILE PATINDEX(‘%[^0-9]%’,@mixedNumStr)> 0

SET @mixedNumStr = REPLACE(@mixedNumStr,SUBSTRING(@mixedNumStr,PATINDEX(‘%[^0-9]%’,@mixedNumStr),1),”)

END

return @mixedNumStr

END

–A sample to use UDF fn_KeepNum

SELECT * FROM dbo.aTable

WHERE dbo.fn_KeepNum(charNumColumn)>=900

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s