October 2, 2008 at 1:25 pm
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?
October 2, 2008 at 1:50 pm
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
October 2, 2008 at 1:54 pm
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?
October 2, 2008 at 2:03 pm
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
October 2, 2008 at 2:23 pm
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.
October 2, 2008 at 3:45 pm
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?
October 3, 2008 at 12:38 am
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
October 3, 2008 at 7:39 am
Thanks a ton!
October 3, 2008 at 7:44 am
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.
October 3, 2008 at 7:54 am
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
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
October 3, 2008 at 7:58 am
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?
October 3, 2008 at 8:06 am
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
October 3, 2008 at 9:41 am
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.
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
October 3, 2008 at 10:52 am
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
March 11, 2014 at 7:30 am
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)')
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply