practical application of REVERSE function?

Question

practical application of REVERSE function?

Viewing 11 posts - 16 through 25 (of 25 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Luis Cazares (7/30/2012)
ColdCoffee (7/30/2012)
sestell1 (7/30/2012)
LEN(Reverse(<string>) is a quick and dirty way to find the length of a string INCLUDING trailing whitespace.
Instead, you can use DATALENGTH function to get the length, inlcuding white spaces
That's true, unless you're using Unicode strings.
DECLARE @FilePath VARCHAR(100),
@FilePath2 NVARCHAR(100)
SET @FilePath = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
SET @FilePath2 = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ '
SELECT LEN(REVERSE(@FilePath)) [LenRevrs]
,DATALENGTH(@FilePath) [Datalength]
,LEN(REVERSE(@FilePath2)) [LenRevrs2]
,DATALENGTH(@FilePath2) [Datalength2]

I have a feeling that something "interesting" may be happening i the code above. I made a slight change to the code. Run the following:

DECLARE @FilePath VARCHAR(100),

@FilePath2 NVARCHAR(100)

SET @FilePath = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

SET @FilePath2 = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

SELECT LEN(REVERSE(@FilePath)) [LenRevrs]

,DATALENGTH(@FilePath) [Datalength]

,LEN(REVERSE(@FilePath2)) [LenRevrs2]

,DATALENGTH(@FilePath2) [Datalength2]

,DATALENGTH(N'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') [Datalength2a]

,LEN(REVERSE(N'ABCDEFGHIJKLMNOPQRSTUVWXYZ ')) [LenRevrs2a]

,@FilePath FilePath

,@FilePath2 FilePath2

into dbo.testrev;

select *, datalength(FilePath2), datalength(RevFilePath2) from dbo.testrev;

Pay close attention to the last two values.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442467

July 30, 2012 at 2:30 pm

#1519420

Hang on, copied the wrong code above.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Teach me to use cntl-Z to remove temporary changes:

DECLARE @FilePath VARCHAR(100),

@FilePath2 NVARCHAR(100)

SET @FilePath = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

SET @FilePath2 = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

SELECT LEN(REVERSE(@FilePath)) [LenRevrs]

,DATALENGTH(@FilePath) [Datalength]

,LEN(REVERSE(@FilePath2)) [LenRevrs2]

,DATALENGTH(@FilePath2) [Datalength2]

,DATALENGTH(N'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') [Datalength2a]

,LEN(REVERSE(N'ABCDEFGHIJKLMNOPQRSTUVWXYZ ')) [LenRevrs2a]

,@FilePath FilePath

,@FilePath2 FilePath2

,REVERSE(@FilePath) [RevFilePath]

,REVERSE(@FilePath2) [RevFilePath2]

into dbo.testrev;

select *, datalength(FilePath2), datalength(RevFilePath2) from dbo.testrev;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

What's your point Lynn? The last two values use datalength on unicode strings and is normal to give the same output.

Am I missing something? Have you slept well lately?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

I sense that the last two values are proper/accurate.. do you sense some discrepancy there Lynn?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

This:

LEN(REVERSE(@FilePath2)) [LenRevrs2] = 36

datalength(RevFilePath2) = 72

The variable @FilePath2 is declared NVARCHAR(100), yet it says it only uses 36 bytes when reversed. That same string written to a table, and then the length of the reversed string is taken and it suddenly uses 72 bytes.

Also, if the original string uses 72 bytes, why does the reverse of the string only use 36 bytes?

Written to disk, it behaves as one expects. Done in memory, it acts as if the reverse is varchar, not nvarchar.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Luis Cazares (7/30/2012)
What's your point Lynn? The last two values use datalength on unicode strings and is normal to give the same output.
Am I missing something? Have you slept well lately?

The variable @FilePath2 is also unicode (NVARCHAR(100)). Why the change in datalength when the value of the string is reversed? It should be returning a NVARCHAR value if an NVARCHAR value is supplied.

Also, really, blaming this on wether I have slept well lately or not?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Lynn Pettis (7/30/2012)
Luis Cazares (7/30/2012)
What's your point Lynn? The last two values use datalength on unicode strings and is normal to give the same output.
Am I missing something? Have you slept well lately?
The variable @FilePath2 is also unicode (NVARCHAR(100)). Why the change in datalength when the value of the string is reversed? It should be returning a NVARCHAR value if an NVARCHAR value is supplied.
Also, really, blaming this on wether I have slept well lately or not?

The blaming on wether you have slept or not is a joke, since I'm watching this as a simple confusion you made as you're comparing apples and oranges.

When using DATALENGTH, we're counting bytes and it returns 72 if it's reverted or not or if it's on memory or on disk.

When using LEN, we're counting characters and it returns 26 or 36 depending if it's reverted or not, but it does not varies from VARCHAR to NVARCHAR.

DECLARE @FilePath VARCHAR(100),

@FilePath2 NVARCHAR(100)

SET @FilePath = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

SET @FilePath2 = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ '

SELECT --With VARCHAR

LEN(@FilePath) [Len] --Returns 26

,LEN(REVERSE(@FilePath)) [LenRevrs] --Returns 36

,DATALENGTH(@FilePath) [Datalength] --Returns 36

,DATALENGTH(REVERSE(@FilePath)) [DatalengthRev] --Returns 36

--With NVARCHAR

,LEN(@FilePath2) [Len2] --Returns 26

,LEN(REVERSE(@FilePath2)) [LenRevrs2] --Returns 36

,DATALENGTH(@FilePath2) [Datalength2] --Returns 72

,DATALENGTH(REVERSE(@FilePath2)) [DatalengthRev2] --Returns 72

--INTO Disk

SELECT @FilePath FilePath

,@FilePath2 FilePath2

,REVERSE(@FilePath) [RevFilePath]

,REVERSE(@FilePath2) [RevFilePath2]

INTO dbo.testrev;

--FROM Disk

SELECT--VARCHAR

DATALENGTH(FilePath) --Returns 36

,DATALENGTH(RevFilePath) --Returns 36

--NVARCHAR

,DATALENGTH(FilePath2) --Returns 72

,DATALENGTH(RevFilePath2) --Returns 72

FROM dbo.testrev;

DROP TABLE dbo.testrev;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

It isn't sleep (or lack there of). It was simple inattention to your original query that confused me. I may have been comparing apples and oranges but I was using what you provided without looking at it with a more critical eye.

Thank you for the slap to head, it was needed.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 10

polkadot (7/30/2012)
OK, so you reverse the expression, find the character you want, perform the operation, and then reverse the expression again to aright it.
I have seen such situations arise, I now realize. Thank you CC and everyone for the responses.

Absolutely not. REVERSE is a pretty expensive function. To find things in the "Tail of the string", you only need to do a single reverse to find the character number of something such as a final slash in a path name and then use something like RIGHT to return the number of characters based on what you found using the single REVERSE.

--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)

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 11

sestell1 (7/30/2012)
LEN(Reverse(<string>) is a quick and dirty way to find the length of a string INCLUDING trailing whitespace.

Oh, be careful, now. LEN(REVERSE()) is comparatively expensive compared to just using DATALENGTH.

--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)