How to get last 10 characters in all rows of a single(same) column?

Question

How to get last 10 characters in all rows of a single(same) column?

elchuru

Default port

Points: 1435
More actions
March 25, 2010 at 7:37 pm

#386582

Hi,
Can anybody suggest me "How to get last 10 characters in all rows of a single(same) column?".
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

elchuru (3/25/2010)
Hi,
Can anybody suggest me "How to get last 10 characters in all rows of a single(same) column?".
Thanks in advance

declare @test-2 varchar(50)

set @test-2 = 'qhonboenoqenfpgqonfnoibnqogqofqqf'

select right(@test, 10)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

elchuru Default port Points: 1435 More actions · Answer 2

elchuru

Default port

Points: 1435

March 25, 2010 at 7:48 pm

#1140106

Thanks a lot.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

No, no, Wayne.... that's just what they'll be expecting him to try!!

I might have said the following because it's more performant than a RIGHT() function.

REVERSE(LEFT(REVERSE(LTRIM(RTRIM(colname)),10))

Please note: People without a sense of humor should just ignore this post.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

The Dixie Flatline (3/25/2010)
No, no, Wayne.... that's just what they'll be expecting him to try!!
I might have said the following because it's more performant than a RIGHT() function.
REVERSE(LEFT(REVERSE(LTRIM(RTRIM(colname)),10))

Poor guy would have had to learn five functions to solve one problem:-D What would have been his tutor's reaction had he used this code :w00t:

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Kingston, that's why I added the footnote.

I considered posting a tally table solution, too, but that was probably overkill. 😛

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Garadin One Orange Chip Points: 29613 More actions · Answer 6

I think I've got you beat Bob. This is pretty performant... runs in well under a second.

CREATE TABLE #Test(

nvcnvarchar(MAX))

CREATE TABLE #Output(

nvcoutnvarchar(MAX))

INSERT INTO #Test(nvc)

SELECT REPLICATE(N'A',500) UNION ALL

SELECT REPLICATE(N'B',600)

-- Declare Variables

DECLARE @nvcnvarchar(MAX),

@iint,

@outnvarchar(MAX)

-- Declare Cursor

DECLARE Test CURSOR GLOBAL DYNAMIC FOR

SELECT nvc

FROM #test

-- Open Cursor

OPEN Test

FETCH NEXT FROM Test

INTO @nvc

WHILE @@FETCH_STATUS = 0

BEGIN -- Cursor Loop

SET @out = ''

SET @i = 0

WHILE @i < LEN(@nvc) + 1

BEGIN

IF @i > LEN(@nvc) - 10

SET @out = @out + SUBSTRING(@nvc,@i,1)

SET @i = @i + 1

END

SET @i = 0

INSERT INTO #Output(nvcout)

SELECT @out

FETCH NEXT FROM Test

INTO @nvc

END-- End Cursor Loop

-- Close and dispose of cursor.

CLOSE Test

DEALLOCATE Test

SELECT * FROM #Output

DROP TABLE #Test

DROP TABLE #Output

Disclaimer: I'm not responsible for you being fired if you actually use that query anywhere that someone can see it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]