| Author: dom 06 Nov 2009 | Member Level: Silver | Rating:  Points: 2 |
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.
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
/* 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
this would help u...
Regards
Jaiho
|
| Author: Mohan 06 Nov 2009 | Member Level: Diamond | Rating: Points: 2 |
Check this link
http://www.dotnetspider.com/resources/29013-SQL-User-defined-Function-remove-numbers-from.aspx
Regards
Mohan Kumar.D
|
| Author: Shameer 08 Nov 2009 | Member Level: Silver | Rating: Points: 2 |
Select * from Production.Product Where Name like ‘%[A-Z]%’
|