Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Removing non-alpha characters & spaces script...

    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 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]' TH EN 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

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

    Change it to count ...don't have much time now,check for other errors.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Dec 2006
    Posts
    3
    Cheers, its all sorted now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •