Checking for a character

  • Can anyone please let me know if they have a sql script which would loop through every single character of a field and find if there are any characters with ascii value > 128. Anyhelp on this will be greatly appreciated.

    TIA

  • I used to need to strip Alpha-numerics from a certain field.  I have changed it to try and fit your needs.  Hope it works...

     

    SET NOCOUNT ON

    DECLARE @CurrentID int,

     @MaxID int,

     @OriginValue varchar(25),

     @TempValue varchar(1),

     @CurrentPosition int,

     @Length int

    SELECT @CurrentID = (SELECT MIN( RowID) FROM AP),

     @MaxID = (SELECT MAX( RowID) FROM AP)

    WHILE @CurrentID <= @MaxID

    BEGIN

     SELECT @OriginValue = ' '

     SELECT @OriginValue = ISNULL( (SELECT [Value] FROM [TableName] WHERE RowID = @CurrentID), '')

     IF @OriginValue <> '' -- OR @OriginValue IS NOT NULL

     BEGIN

      SELECT @CurrentPosition = 1

      SELECT @Length = (SELECT LEN( RTRIM( LTRIM( @OriginValue))))

      WHILE @CurrentPosition <= @Length 

      BEGIN

       SELECT @TempValue = (SELECT SUBSTRING( @OriginValue, @CurrentPosition, 1))

        IF @TempValue = CHAR(128)

        BEGIN

         -- DO SOMETHING HERE... 

        END

       SELECT @CurrentPosition = @CurrentPosition + 1

      END

     END

    I wasn't born stupid - I had to study.

  • oops... should have been IF @TempValue > 128

    Sorry...

    I wasn't born stupid - I had to study.

  • Not too sure what you want to retrieve but this will return yes/no on a character by character basis

     

    declare @i as integer

    declare @mystring as nvarchar(50)

    set @mystring = 'aad+asa'

    set @i = 0

    while @i < (len(@mystring)+1)

    begin

         set @i = @i + 1

         if (select unicode(substring(@mystring,@i,1))) < 128

         begin

             print 'yes'

         end

         else

         if (select unicode(substring(@mystring,@i,1))) > 128

         begin

             print 'no'

         end

    end


    ------------------------------
    The Users are always right - when I'm not wrong!

  • What i am trying to get from a table is all rows of a specific field say name which has any character with ascii>128 . Any script that can help me get the results.

    TIA

  • If you will look at my script, I have identified all records from a field in a table that have character 128 and above.  I did not know what you wanted to do with this information, but where I have written: -- Do something here.  You could simply select that record based upon the ID from your table, (I used an old script of mine and the table I used was called AP). 

    I wasn't born stupid - I had to study.

  • This will do it for you but is not too efiificent:

    ------------------------------------------------------------

    declare @i as integer

    declare @mystring as nvarchar(255)

    create table #tmp_results

    (results nvarchar(255))

    DECLARE column_name_cursor CURSOR FOR

    SELECT column_name FROM table_name

    OPEN column_name_cursor

    FETCH NEXT FROM column_name_cursor

    INTO @mystring

    WHILE @@FETCH_STATUS = 0

    BEGIN

     set @i = 0

     while @i < (len(@mystring)+1)

     begin

          set @i = @i + 1

          if (select unicode(substring(@mystring,@i,1))) > 128

          begin

          insert into #tmp_results values(@mystring)

          end

     

     end

       FETCH NEXT FROM column_name_cursor

       INTO @mystring

    END

    CLOSE column_name_cursor

    DEALLOCATE column_name_cursor

    select distinct(results) from #tmp_results

    drop table #tmp_results

    --------------------------------------------------------------


    ------------------------------
    The Users are always right - when I'm not wrong!

  • Superfast set based query (0.093 sec for a table of 10000+ records of 40-50 characters):

    CREATE TABLE [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

    while @i < 8000

    begin

    Insert into dbo.Numbers Default Values

    set @i = @i + 1

    end

    Select Distinct O.PkObjSQL, O.Name from dbo.ObjSQL O cross join dbo.Numbers N

    Where N.PkNumber 128

    This select makes a cross join for each PkNumber that is smaller or equal to the lenght of the string, then it uses the PkNumber to do the substring and test each character for an ascii number bigger than 128.

  • Just to say that I found Jonathan Stokes code very useful. Thanks!

  • Johnathan's code is good... but check the performance and resource usage when compared to the numbers-table solution that Remi squeezed off...

    Hey Remi... nice loop on the table creation

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 11/2/2004

     

    I was still in my first months then .

  • Heh... sorry about that... saw the recent post and didn't check the dates on the history.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • NP.  Those are good memories.  Makes me see how much I've grown in the last 2 years.

Viewing 13 posts - 1 through 12 (of 12 total)

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