Help! - Removing non-alpha and spaces from a field script..

Question

Post reply

Help! - Removing non-alpha and spaces from a field script..

dnf999

SSC Eights!

Points: 870
More actions
December 6, 2006 at 3:15 am

#118236

Hi I am trying to strip out any non-alpha characters from a field.
i.e. Field = ABC"_IT8*$              should return: ABCIT8
I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:
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-2 numeric
-- @sqlstring varchar(500)
SET @Counter = 1
SET @max-2 = (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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ijaz SSCommitted Points: 1912 More actions · Answer 1

You have missed the Begin and End Block for the inner while loop.

You should need to put this after the while and end when you are subtract from the index.

Cheers

cheers

dnf999 SSC Eights! Points: 870 More actions · Answer 2

Thanks, the loop works properly now, but I can't seem to get the field to update correctly.... any ideas please??

I need to the results like:

MATCH Supplier Name:

ABC^ 123 (* to ABC123

DECLARE @index SMALLINT,

@MATCH_Supplier_name varchar(500),

@Counter numeric,

@Max numeric

-- @sqlstring varchar(500)

SET @Counter = 1

SET @max-2 = (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

BEGIN

UPDATE SUPPLIER_TABLE_TEST

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

ijaz SSCommitted Points: 1912 More actions · Answer 3

HI,

The inner while should code should be replaced with this and it will work fine for you.

WHILE @index > = 1

begin

SET @MATCH_Supplier_name = CASE

WHEN SUBSTRING(@MATCH_Supplier_name, @index, 1) LIKE '[a-zA-Z]' OR SUBSTRING(@MATCH_Supplier_name, @index, 1) LIKE '[0-9]' THEN @MATCH_Supplier_name

ELSE REPLACE(@MATCH_Supplier_name,SUBSTRING(@MATCH_Supplier_name, @index, 1),'')

END

SET @index = @index - 1

PRINT @MATCH_Supplier_name

end

cheers

ijaz SSCommitted Points: 1912 More actions · Answer 4

Try this...

DECLARE @index SMALLINT,

@MATCH_Supplier_name varchar(500),

@Counter numeric,

@max-2 numeric

-- @sqlstring varchar(500)

SET @Counter = 1

SET @max-2 = (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

BEGIN

SET @MATCH_Supplier_name = CASE

WHEN SUBSTRING(@MATCH_Supplier_name, @index, 1) LIKE '[a-zA-Z]' OR SUBSTRING(@MATCH_Supplier_name, @index, 1) LIKE '[0-9]' THEN @MATCH_Supplier_name

ELSE REPLACE(@MATCH_Supplier_name,SUBSTRING(@MATCH_Supplier_name, @index, 1),'')

END

SET @index = @index - 1

--PRINT @MATCH_Supplier_name

END

SET @Counter = @Counter + 1

END

cheers

dnf999 SSC Eights! Points: 870 More actions · Answer 5

dnf999

SSC Eights!

Points: 870

December 6, 2006 at 4:54 am

#676173

Excellent. Thanks!

ijaz SSCommitted Points: 1912 More actions · Answer 6

ijaz

SSCommitted

Points: 1912

December 6, 2006 at 5:01 am

#676175

You are always welcome.

cheers

schleep SSChampion Points: 12583 More actions · Answer 7

Try this function.

@cStripPat is the chars to remove. In your case, '%[^a-zA-Z]%'

HTH

P

IF OBJECT_ID('dbo.ufn_XU_PatStrip') IS NOT NULL

DROP FUNCTION dbo.ufn_XU_PatStrip

GO

CREATE FUNCTION dbo.ufn_XU_PatStrip

(@cStringNVARCHAR(4000),

@cStripPatNVARCHAR(4000))

RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @nposINTEGER

SELECT @npos = PATINDEX(@cStripPat, @cString)

WHILE @npos > 0

BEGIN

SELECT @cString = RTRIM(LTRIM(STUFF(@cString, @npos, 1, '')))

SELECT @npos = PATINDEX(@cStripPat, @cString)

END

RETURN @cString

END

GO

Lowell SSC Guru Points: 323518 More actions · Answer 8

I'm suprised noone mentioned the regular expression extended stored procedures:

http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp

with that you can search, select or replace things patterns like this ; it's a bit easier than looping thru characters. it's also faster;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Robert Davis One Orange Chip Points: 28027 More actions · Answer 9

Or the article I wrote, Practical Uses of PatIndex: http://www.sqlservercentral.com/columnists/rdavis/practicalusesofpatindex.asp

I demonstrate a much simpler approach to this:

Create Function dbo.fnDigitsOnly(

@value varchar(50))

Returns varchar(50)

As

Begin

While PatIndex('%[^0-9]%', @value) > 0

Begin

Set @value = Stuff(@value, PatIndex('%[^0-9a-zA-Z]%', @value), 1, '')

End

Return @value

End

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]