Strip out non-numeric or alpha characaters

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 2000 Forums

Transact-SQL (2000)

Strip out non-numeric or alpha characaters

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-05 : 06:31:36

Hi is there a simple single statement which I can use to Strip out non-numeric or alpha characaters?

i.e.

Country Clean_Country
------- ------------
UK_ UK
U&S US
(Canada) Canada
Peru6 Peru

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 06:42:42

What about spaces?

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-05 : 06:53:17

yes spaces Stripped also.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 06:55:38

So "Trinidad & Tobago" will be "TrinidadTobago"?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:00:21

[code]CREATE FUNCTION dbo.fnStripCharacters
(
@Content VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Index SMALLINT,
@Out VARCHAR(8000)

SELECT @Index = LEN(@Content),
@Out = ''

WHILE @Index >= 1
SELECT @Out = CASE
WHEN SUBSTRING(@Content, @Index, 1) LIKE '[a-zA-Z]' THEN SUBSTRING(@Content, @Index, 1)
ELSE ''
END + @Out,
@Index = @Index - 1

RETURN @Out
END[/code]

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-05 : 09:49:51

Hi Peter, Thats works great! Thanks!

I just had one query though, what does the ' + @Out,' do after END?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 09:51:43

It is concatenating each and one a-to-z-character from the original string.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 10:28:30

1 When you send data, filter them and send only relevent data
2 Other example http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-05 : 11:50:14

I have tried to tailor the above script so that it strips out the non-alpha and spaces within a field of a table. The field required to strip out is: Match_Supplier_Name

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)

SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources