Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-03-24 : 23:25:19

Hi Guys,
Want to know how i can accomplish this kinda problem,

ZIP
43987
345A8
ABCDE
67895

Note:- How i can clean my data, How i can delete only "ALPHA" from ZIP Column.

CODE
ABC
4DEF
123
GHI

Note:- How i can clean my data, How i can delete only "Numeric" from COde Column.

Thanks for help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-24 : 23:44:15

WHERE zip LIKE '%[^0-9]%'

See here

jscot
Posting Yak Master

106 Posts

Posted - 2011-03-24 : 23:59:24

RUSSEL THANKS FOR PROMPT REPLY, BUT HOW I CAN DELETE ALPHA FROM ZIP AND NUMERIC FROM CODE, I WANT TO USE UPDATE STATEMENT?

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-03-25 : 07:55:56

quote:
Originally posted by jscot

Hi Guys,
Want to know how i can accomplish this kinda problem,

ZIP
43987
345A8
ABCDE
67895

Note:- How i can clean my data, How i can delete only "ALPHA" from ZIP Column.

CODE
ABC
4DEF
123
GHI

Note:- How i can clean my data, How i can delete only "Numeric" from COde Column.

Thanks for help.

Try this once....

CREATE TABLE clean(zip varchar(10),code varchar(10))
GO
INSERT INTO clean VALUES ('43987','ABC')
INSERT INTO clean VALUES ('345A8','4DEF')
INSERT INTO clean VALUES ('ABCDE','123')
INSERT INTO clean VALUES ('67895','GHI')
GO
SELECT * FROM clean
go
CREATE FUNCTION dbo.DelitingNum(@code varchar(100))
RETURNS varchar(100)
As
BEGIN
DECLARE @i int
DECLARE @a char
DECLARE @var1 varchar(100)
SET @var1=@code
SET @i=0
WHILE @i<=(SELECT LEN(@var1))
BEGIN
SELECT @a=SUBSTRING(@var1,LEN(@var1)-@i,1)
DECLARE @ii int
SELECT @ii = ISNUMERIC((SELECT @a))
IF (@ii =1)
SELECT @var1=REPLACE(@var1,@a,'1')
SET @i=@i+1
END
SELECT @var1=REPLACE(@var1,'1','')
RETURN(@var1)
End
go
------------------------------------
CREATE FUNCTION dbo.DelitingAlpha(@Zip varchar(100))
RETURNS varchar(100)
As
BEGIN
DECLARE @i int
DECLARE @a char
DECLARE @var1 varchar(100)
SET @var1=@Zip
SET @i=0
WHILE @i<=(SELECT LEN(@var1))
BEGIN
SELECT @a=SUBSTRING(@var1,LEN(@var1)-@i,1)
DECLARE @ii int
SELECT @ii = ISNUMERIC((SELECT @a))
IF (@ii =0)
SELECT @var1=REPLACE(@var1,@a,'a')
SET @i=@i+1
END
SELECT @var1=REPLACE(@var1,'a','')
RETURN(@var1)
End
go
UPDATE clean
SET zip=dbo.DelitingAlpha(Zip),code=dbo.DelitingNum(code)
go
SELECT * FROM clean

sathish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources