Removing Characters from Column
-
I have a column that is VARCHAR and I want to remove all characters that are not numeric. The columns contain data like this:
56983-C
2495684P
2566789-P
Anyone have any suggestions? Thanks in advance.
-
I got this from Remi. Its a pretty darn good function and the table Numbers is useful for itterating...
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Numbers]
GO
CREATE TABLE [dbo].[Numbers]( [PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED( [PkNumber]) ON [PRIMARY] ) ON [PRIMARY]
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @i as int
SET @i = 0
--I use this table for string operations as well, but in this case we could stop at 64.
WHILE @i < 8000
BEGIN
INSERT INTO dbo.Numbers DEFAULT VALUES
SET @i = @i + 1
END
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.RemoveChars (@Input as varchar(8000))
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Return AS varchar(8000)
SET @Return = ''
SELECT @Return = @Return + SUBSTRING( @Input, PkNumber, 1)
FROM dbo.Numbers
WHERE ASCII( SUBSTRING( @Input, PkNumber, 1)) BETWEEN 48 AND 57
AND PkNumber <= LEN( @Input)
RETURN @Return
END
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT dbo.RemoveChars('l3l45kjhsf87y3')
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP Function RemoveChars
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Numbers]
GO
I wasn't born stupid - I had to study.
-
Glad to see someone's using my stuff once in a while
. -
Don't let your head get too big - it is only once and while.... heheeee (jus' kiddin').
I wasn't born stupid - I had to study.
-
Don't worry about that... stil got plenty of room left to pass through the door.
-
Thanks again gentlemen. One day I'll make a usefull contribution to this site, (aside from helping inflate others heads).
-
Don't worry about it... Farrell's gonne come around one of these days
. -
My head is big enough... just ask my wife. She'll be happy to complain about it to you.
I wasn't born stupid - I had to study.
-
Send her in
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply