Results 1 to 9 of 9

Thread: unescape?

  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: unescape?

    How do I unescape a string in SQL?

    Say I've got variable @mySubject varchar(255), and the following:

    SELECT @mySubject=subject FROM forum_threads WHERE id=@existingID

    now @mySubject is going to be used later in the stored proc as an email subject field, but the trouble is, in its current form a typical subject might look like "This%20is%20the%20subject".

    Now I could just replace all instances of "%20" with " " - but then do I do that for all question marks? all commas? and so on?

    There has to be a better way....

  2. #2
    Join Date
    Jul 2002
    Posts
    229

    Re: unescape?

    Could the ESCAPE keyword have something to do with this?

  3. #3
    Join Date
    Aug 2002
    Posts
    20
    Hi,

    Use REPLACE. Se books online of more information.

    SELECT @mySubject=REPLACE(subject,'%20',' ') FROM forum_threads WHERE id=@existingID


    /Mats

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    Coolberg - not really. See, ESCAPE just defines an escape character. What I'm talking about is the result of the escape() function found, for example, in JSCRIPT. This returns a string with, AFAIK, all non-alphanumeric characters converted to their hexadecimal unicode values. I use it in ASP pages to stop single quotes and other nasties being dropped into the database.

    Mada - But what about all the other character codes? To catch, for example, a few of the more common punctuation elements, I'd have to do something like:

    SELECT @mySubject=subject FROM forum_threads WHERE id=@existingID
    SELECT @mySubject=REPLACE(@mySubject, '%20', ' ')
    SELECT @mySubject=REPLACE(@mySubject, '%21', '!')
    SELECT @mySubject=REPLACE(@mySubject, '%3F', '?')
    SELECT @mySubject=REPLACE(@mySubject, '%27', '"')
    SELECT @mySubject=REPLACE(@mySubject, '%2C', ',')
    SELECT @mySubject=REPLACE(@mySubject, '%2E', '.')
    SELECT @mySubject=REPLACE(@mySubject, '%22', '''')
    SELECT @mySubject=REPLACE(@mySubject, '%25', '%')
    SELECT @mySubject=REPLACE(@mySubject, '%2D', '-')


    - which doesn't seem like good practice? Doesn't SQL have ANYTHING for dealing with hexadecimal unicode values?

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Have you read up on the UNICODE and NCHAR functions in Books Online? These may be what you are looking for.

    IMHO you probably will need to roll your own function for converting strings form UNICODE delimited back to regular text. Seems like alot of work just to deal with the single/double quote problem.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    Ugh. You're right, it IS a lot of work.

    I always escape strings before I drop them into a database, it's the simplest way of avoiding the single/double quote problem. But only because I only ever get stuff out of databases via ASP pages, where I can unescape them again. Obviously I can't do that here as there's no ASP page involved.

    The trouble with UNICODE - and NCHAR - is that they deal with integer values, whereas ASP escapes strings using hexadecimal. The unicode value for a single quote is "39", but in ASP single quotes are dropped into the database as "%22".

    So - I can see three options:

    1. Work out how to convert hexadecimal into integer, convert all my hexadecimal unicode values to integer unicode values and THEN convert those to their unicode characters.

    2. Stick with simply using REPLACE, like in the post above (although I seemed to get an error before - is my syntax right?) and be content with catching most common punctuation.

    3. Don't escape stuff. Find another way of stopping single quotes and other nasties going into the database.

    Any suggestions? Please...?

  7. #7
    Join Date
    Aug 2002
    Posts
    20
    Hi,


    Excuse me, but I didn’t read our question carefully enough. As you know T-sql is basically design for select, insert, delete and update. As computer languages it can’t provide advanced string manipulations. If there is any help to know, you can convert your string into varbinary.

    Select convert(varbinary,'%20')
    Returns
    0x253230

    NCHAR returns the code for the one character. Example;

    SELECT NCHAR(‘%’),NCHAR(UNICODE(‘%’)

    If I had your problem I had made a cursor how read from a temporary table in which I can define the characters I want to replace and with what.

    Sorry that I took your time…

    /Mats

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    okay how about:

    Code:
    CREATE FUNCTION HexStringToINT (
    @Hex varchar(255))
    RETURNS int
    AS
    BEGIN
      declare @Char char(1), @Multiply int, @Value int, @Ans int
      set @Hex = reverse(@Hex)
      select @Ans      = 0
           , @Multiply = 0
      while @Multiply + 1 <= datalength(@Hex) begin
        set @Char = substring(@Hex,@Multiply + 1,1)  
        if @Char between '0' and '9'
          set @Value = cast(@Char as int)
        else
          select @Value = case @Char when 'A' then 10
                                     when 'B' then 11
                                     when 'C' then 12
                                     when 'D' then 13
                                     when 'E' then 14
                                     when 'F' then 15
                                     else 0
                          END
        set @Ans = @Ans + (@Value * power(16,@Multiply))
      
        set @Multiply = @Multiply + 1
      end
      return @Ans
    END
    GO
    
    
    declare @Text nvarchar(50), @NewText nvarchar(50), @1stpos int, @2ndpos int
    select @Text = 'This%20is%20the%20subject'
         , @NewText = ''
         , @1stpos = 1
         , @2ndpos = charindex('%',@Text,1)
    
    while (@2ndpos > 0) begin
      set @NewText = @NewText + substring(@Text,@1stpos,@2ndpos - @1stpos)
      set @NewText = @NewText + nchar(master.dbo.HexStringToINT(substring(@Text,@2ndpos + 1,2)))
    
      set @1stpos = @2ndpos + 3
      set @2ndpos = charindex('%',@Text,@1stpos)
    end
    
    select @Text as 'Original Text'
    select @NewText + substring(@Text,@1stpos,50) as 'New Text'


    although IMHO I would opt for dealing with single quotes and let the rest fly...
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Jan 2002
    Posts
    189
    Good grief. I'd worry for my sanity if I understood half of that

    Option three is looking pretty attractive, then..

    Thanks to everybody for the suggestions though.

Posting Permissions

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