How do you pad left like using T-SQL query?

Question

Post reply

How do you pad left like using T-SQL query?

Alex Jordan

SSC Eights!

Points: 880
More actions
November 17, 2014 at 8:06 pm

#313260

Trying to pad zero's to the left of number to find n.Num LIKE
SELECT * FROM tblNumbers n WHERE n.Num LIKE 'RIGHT('00000' + CONVERT(VARCHAR, CONVERT(INT, '1234')), 5)%' ORDER BY n.Num
Incorrect syntax near '00000'.

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

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

pietlinden SSC Guru Points: 63578 More actions · Answer 1

pietlinden

SSC Guru

Points: 63578

November 17, 2014 at 8:41 pm

#1759140

SELECT FORMAT(2504,'0000000');

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 2

Alex Jordan (11/17/2014)
Trying to pad zero's to the left of number to find n.Num LIKE
SELECT * FROM tblNumbers n WHERE n.Num LIKE 'RIGHT('00000' + CONVERT(VARCHAR, CONVERT(INT, '1234')), 5)%' ORDER BY n.Num
Incorrect syntax near '00000'.

Remove the single quote just prior to RIGHT and remove the trailing % and the single quote that follows it.

As a bit of a sidebar, what is the need that you have for such padding of numeric data? I sure wouldn't store things in the database this way. Hopefully, tblNumbers doesn't have such a mistake in it.

Also, I believe that you'll find that FORMAT is quite a bit slower than the traditional method you're using.

If '1234' is actually an integer, like I believe it should be, then you can cheat like hell...

RIGHT(N+100000,5) --Notice the 5 zeros, "N" represent the 1234 int

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

Alex Jordan SSC Eights! Points: 880 More actions · Answer 3

Remove the single quote just prior to RIGHT and remove the trailing % and the single quote that follows it.

Thanks Jeff. I went a little single quote happy.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 4

If you're padding a numeric value, I'd suggest using the built-in function:

STR()

I suspect that will perform better than writing the same thing out in your own code.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 5

ScottPletcher (11/18/2014)
If you're padding a numeric value, I'd suggest using the built-in function:
STR()
I suspect that will perform better than writing the same thing out in your own code.

Actually, STR() is pretty slow (more than 2X slower) and uses more than twice the CPU. I won't use it for padding when it's so simple to do otherwise. Here's a link for the performance testing I did a couple of years ago. Look for the "STR() is... SLOW!" section title in the article.

http://www.sqlservercentral.com/articles/T-SQL/71565/

Before anyone starts, yes, I agree... The difference in actual CPU time has dramatically decreased over the years. Here's what the million row run results in the article come back as now...

========== Cast, Concatenate, and Size ==========

SQL Server Execution Times:

CPU time = 375 ms, elapsed time = 373 ms.

========== The STR() Method is SLOWER ==========

SQL Server Execution Times:

CPU time = 936 ms, elapsed time = 939 ms.

There's still that nagging more-than-2X-the-resources and duration problem and a million rows really isn't anything anymore. If anyone prefers to use STR() for its simplicity, that's their choice, but I can't see using something that I already know is at least twice as bad.

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

How do you pad left like using T-SQL query?

Cookies on SQLServerCentral