Creating a ROT13 Function

  • I'm trying to create a ROT13 function without having to use the xp_rot13

    So I've got this

    SELECT TOP 11000

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    CREATE TABLE rot13_table (source varchar(1) COLLATE Latin1_General_CS_AS ,result varchar(1)COLLATE Latin1_General_CS_AS )

    insert into rot13_table

    SELECT

    'A','N'

    UNION ALL

    SELECT

    'B','O'

    UNION ALL

    SELECT

    'C','P'

    UNION ALL

    SELECT

    'D','Q'

    UNION ALL

    SELECT

    'E','R'

    UNION ALL

    SELECT

    'F','S'

    UNION ALL

    SELECT

    'G','T'

    UNION ALL

    SELECT

    'H','U'

    UNION ALL

    SELECT

    'I','V'

    UNION ALL

    SELECT

    'J','W'

    UNION ALL

    SELECT

    'K','X'

    UNION ALL

    SELECT

    'L','Y'

    UNION ALL

    SELECT

    'M','Z'

    UNION ALL

    SELECT

    'N','A'

    UNION ALL

    SELECT

    'O','B'

    UNION ALL

    SELECT

    'P','C'

    UNION ALL

    SELECT

    'Q','D'

    UNION ALL

    SELECT

    'R','E'

    UNION ALL

    SELECT

    'S','F'

    UNION ALL

    SELECT

    'T','G'

    UNION ALL

    SELECT

    'U','H'

    UNION ALL

    SELECT

    'V','I'

    UNION ALL

    SELECT

    'W','J'

    UNION ALL

    SELECT

    'X','K'

    UNION ALL

    SELECT

    'Y','L'

    UNION ALL

    SELECT

    'Z','M'

    INSERT INTO rot13_table

    SELECT

    lower(source),

    lower(result)

    FROM

    rot13_table

    CREATE FUNCTION rot13 (@inputstr varchar(max))

    RETURNS varchar(max)

    AS

    BEGIN

    DECLARE @vals varchar(1)

    DECLARE @outputstr varchar(max)

    DECLARE @test-2 TABLE (val varchar(1) COLLATE Latin1_General_CS_AS)

    INSERT INTO @test-2

    SELECT

    val = substring(@inputstr,N,1)

    FROM

    Tally

    where

    len(substring(@inputstr,N,1)) > 0

    update @test-2

    set a.val = coalesce(b.result,a.val)

    FROM

    @test-2 a LEFT JOIN rot13_table b ON

    a.val = b.source

    declare csr_rot13 cursor for

    SELECT

    val

    FROM @test-2

    OPEN csr_rot13

    FETCH NEXT from csr_rot13 into @vals

    WHILE @@fetch_status = 0

    BEGIN

    set @outputstr = @outputstr + @vals

    FETCH NEXT from csr_rot13 into @vals

    END

    RETURN @outputstr

    END

    I'd also love to remove the RBAR (i.e. the Cursor) from it as well...

    When I run

    select

    rot13val = dbo.rot13('Mark')

    I get NULL instead of Znex

    but I'm stumped... any tips?



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • As I've read from our "SQL Obi-wan Kenobis" (Jeff Moden & Matt Miller 😉 ) this would be a typical case to use a CLR module.

    Don't use the xp, but download and convert a free c-copy to c# and implement it as a clr module.

    Should outperform any TSQL string function.

    open source rot-13:http://www.miranda.org/~jkominek/rot13/

    feeling the force already ? :w00t:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The problem with this plan is that CLR doesn't work on SQL 2000.

    Yes this is the SQL 2005 forum. But... what I'm working on needs to run on both... I suppose that means I should ask the thread be moved?



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • mtassin (10/2/2008)


    The problem with this plan is that CLR doesn't work on SQL 2000.

    Yes this is the SQL 2005 forum. But... what I'm working on needs to run on both... I suppose that means I should ask the thread be moved?

    Ah, yes, SQL2000 wasn't mentioned and the forum is indeed 2005 :Whistling: ...

    Keep in mind you've used a _CS collation meaning you should also build translations for é è à ç ù ö ,...

    Since your function will be single threaded, did you check Jeffs trick using a variable capturing the ongoing result ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Well the typical string in this case that I'm considering ROT13'ing is about 10 characters long.

    It's a device id... we have several devices that connect to a fax server. Several hundred customers connect to the application and I'm writing code to extract part of the data from them. The problem is, their remote nodes will reconnect and possibly submit faxes after I've passed by and packaged them up to archive them.

    So what I wanted to do was rename the nodes before I built up the package. With something I could pass into ROT13 again to get the correct name back for the destination.

    I didn't want to mess around with something undocumented that may not be supported in the future either... So the UPDATE field = field = field stuff was something I wanted to avoid if possible.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Actually - if I were to ignore the cursor in your code for a minute, all you need to do to make your code work is to initialize the @outputstr variable, with ''.

    Do it before you start using it inside of that C word I am trying to ignore...:)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Our best friend ... BOL ... guided me on this one :w00t:

    /*

    http://www.sqlservercentral.com/Forums/Topic579917-338-1.aspx

    ROT13 ref http://en.wikipedia.org/wiki/ROT13

    */

    CREATE FUNCTION dbo.ufn_ROT13 (@inputvar varchar(128))

    RETURNS varchar(128)

    AS

    /*

    declare @inputvar varchar(128)

    set @inputvar = 'abcdefghijklmnopqrstuvwxyz'

    select @inputvar = @inputvar + upper(@inputvar) + 'éèàçêëöù'

    --Set @inputvar='The Quick Brown Fox Jumps Over The Lazy Dog.'

    */

    BEGIN

    -- Create the variables for the current character string position

    -- and for the character string.

    DECLARE @position int

    , @outputvar varchar(128)

    , @ASCIIValue int

    -- Initialize the variables.

    SET @position = 1

    SET @outputvar = ''

    WHILE @position <= DATALENGTH(@inputvar)

    BEGIN

    set @ASCIIValue = ASCII(SUBSTRING(@inputvar, @position, 1))

    SELECT @outputvar = @outputvar

    + CHAR(case when @ASCIIValue between 65 and 77

    OR @ASCIIValue between 97 and 109

    then @ASCIIValue + 13

    when @ASCIIValue between 78 and 90

    OR @ASCIIValue between 110 and 122

    then @ASCIIValue - 13

    else @ASCIIValue

    end)

    SET @position = @position + 1

    END

    return (@outputvar)

    END

    go

    Select dbo.ufn_ROT13 ('alzdbaALZDBAélèveSchön') as [alzdbaALZDBAélèveSchön]

    go

    /*

    result

    alzdbaALZDBAélèveSchön

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

    nymqonNYMQONéyèirFpuöa

    */

    Select dbo.ufn_ROT13 ('The Quick Brown Fox Jumps Over The Lazy Dog.') as [The Quick Brown Fox Jumps Over The Lazy Dog.]

    go

    /*

    result

    The Quick Brown Fox Jumps Over The Lazy Dog.

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

    Gur Dhvpx Oebja Sbk Whzcf Bire Gur Ynml Qbt.

    */

    Usable for SQL2005 as well as for SQL2000 :hehe:

    This is a good candidate for CLR comparison 😀

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks a ton!



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Matt Miller (10/2/2008)


    Actually - if I were to ignore the cursor in your code for a minute, all you need to do to make your code work is to initialize the @outputstr variable, with ''.

    Do it before you start using it inside of that C word I am trying to ignore...:)

    Well since it's mostly an exercise for my personal enlightenment.. do you see a way to phase the C word out? I literally felt ill when I began typing DECLARE csr_

    I just couldn't see a way to go back through the string I had split into a table and put it back together again.

    I have a similar problem with some other code that has to concatenate a message split across multiple rows... other than stepping through putting the message together one string at a time... I had no way that I could think of to concatenate N strings together in the proper order.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Had to try it, guys...

    [font="Courier New"]DECLARE @StringIn VARCHAR(200), @StringOut VARCHAR(200)

    SET @StringIn = 'The Quick Brown Fox Jumps Over The Lazy Dog.'

    SET @StringOut = ''

    SELECT @StringOut = @StringOut + CHAR(CASE WHEN ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 65 AND 77

                                         OR ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 97 AND 109

                                                                            THEN ASCII(SUBSTRING(@StringIn, number, 1)) + 13

                                    WHEN ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 78 AND 90

                                         OR ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 110 AND 122

                                                                            THEN ASCII(SUBSTRING(@StringIn, number, 1)) - 13

                                    ELSE ASCII(SUBSTRING(@StringIn, number, 1))

                                END)

    FROM Numbers WHERE number <= LEN(@StringIn)

    SELECT @StringOut AS 'The Quick Brown Fox Jumps Over The Lazy Dog.'

    [/font]

    Results:

    'The Quick Brown Fox Jumps Over The Lazy Dog.'

    Gur Dhvpx Oebja Sbk Whzcf Bire Gur Ynml Qbt.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • mtassin (10/3/2008)


    Matt Miller (10/2/2008)


    Actually - if I were to ignore the cursor in your code for a minute, all you need to do to make your code work is to initialize the @outputstr variable, with ''.

    Do it before you start using it inside of that C word I am trying to ignore...:)

    Well since it's mostly an exercise for my personal enlightenment.. do you see a way to phase the C word out? I literally felt ill when I began typing DECLARE csr_

    I just couldn't see a way to go back through the string I had split into a table and put it back together again.

    I have a similar problem with some other code that has to concatenate a message split across multiple rows... other than stepping through putting the message together one string at a time... I had no way that I could think of to concatenate N strings together in the proper order.

    Well - it's going to be reasonably hard to do without using the "running totals" method which you mentioned earlier was something you wanted to avoid. That would be the approach I would take for 2000, and I would take on a CLR option for 2005.

    That being said - let me think on it a bit and see what I might come up with.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Chris Morris (10/3/2008)


    Had to try it, guys...

    [font="Courier New"]DECLARE @StringIn VARCHAR(200), @StringOut VARCHAR(200)

    SET @StringIn = 'The Quick Brown Fox Jumps Over The Lazy Dog.'

    SET @StringOut = ''

    SELECT @StringOut = @StringOut + CHAR(CASE WHEN ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 65 AND 77

                                         OR ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 97 AND 109

                                                                            THEN ASCII(SUBSTRING(@StringIn, number, 1)) + 13

                                    WHEN ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 78 AND 90

                                         OR ASCII(SUBSTRING(@StringIn, number, 1)) BETWEEN 110 AND 122

                                                                            THEN ASCII(SUBSTRING(@StringIn, number, 1)) - 13

                                    ELSE ASCII(SUBSTRING(@StringIn, number, 1))

                                END)

    FROM Numbers WHERE number <= LEN(@StringIn)

    SELECT @StringOut AS 'The Quick Brown Fox Jumps Over The Lazy Dog.'

    [/font]

    Results:

    'The Quick Brown Fox Jumps Over The Lazy Dog.'

    Gur Dhvpx Oebja Sbk Whzcf Bire Gur Ynml Qbt.

    Cheers

    ChrisM

    Indeed, that's the solution that works fastest but uses a table.

    I wanted to avoid that table usage because it will be contained _in_ a function and can be considered a "hidden join".

    Especially if later some one would perform a one on one transformation to a clr module, (s)he might be

    tempted to actualy code that table access !! and pay the price.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Yeah I see your point now. It's easy enough to create a short numbers table on the fly

    SELECT CAST(CAST(d1.Number AS VARCHAR(2)) + CAST(d2.Number AS VARCHAR(2)) AS INT) AS number

    FROM (

    SELECT 0 AS Number UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 ) d1

    CROSS JOIN (

    SELECT 0 AS Number UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 ) d2

    but - without checking - it's probably expensive.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris (10/3/2008)


    ...

    but - without checking - it's probably expensive.

    Yep, checking is what makes this job worth the while :hehe:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I realize it's been 6 years... but interestingly enough this came back to me...

    I can only say this fix works on SLQ 2012, because I don't have 2005 or earlier to try on anymore.

    But this is set based... not any faster than the earlier string iterative ones... but figured I'd contribute back to my own thread.

    I steal the CTE Tally table that's part of the the DelimitedSplit8k function (with a couple changes most notably the removal of 0 because there aren't any delimitters for this)

    CREATE FUNCTION ufn_Rot13_TV(@pstring varchar(8000))

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...

    -- enough to cover VARCHAR(8000)

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS (

    SELECT TOP (case when datalength(@pstring) < 100 then 100 else DATALENGTH(@pstring) end) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    )

    ,ROT13 AS (

    SELECT

    N,

    startchar = char(N),

    EndChar = char(case WHEN N BETWEEN 97 AND 122 THEN

    case when N-13 < 97 then N + 13 else N-13 end

    ELSE Case when N-13 < 65 then N+13 else N-13 end

    end)

    FROM

    cteTally where N between 97 and 122 OR N between 65 and 90

    ),

    ROT13nd as (

    SELECT

    a.N,

    StartChar = substring(@pstring,a.N,1)

    ,endchar = isnull(b.endchar,substring(@pstring,a.N,1))

    FROM

    ctetally a

    left join rot13 b on

    ascii(substring(@pstring,a.N,1)) = b.N--b.startchar

    where a.N <= datalength(@pstring)

    )

    select

    rot13str = (select endchar FROM rot13nd order by N FOR XML PATH(''),type).value('.','varchar(8000)')



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 15 posts - 1 through 14 (of 14 total)

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