...Technology Simplified

Tuesday, September 4, 2012

Extract characters from string in SQL Server

No comments :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_GetAlphabets]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END


--SELECT dbo.udf_GetAlphabets('ABC1234DEFGH789')


Instead if you want to retrieve only numeric values from string replace the pattern '%[^A-Z]%' with '%[^0-9]%'

No comments :

Post a Comment