developer.* Blogs - SQL Server T-SQL LPAD & RPAD Functions (String Padding Equivalent to PadLeft & PadRight) - Comments

Also check out a related

Guest — Wed, 24 Jan 2007 23:09:10 -0800

Also check out a related post, "Hard Coded Limits Considered Harmful," on my blog.

Because there is an alternative to the hard coded values present in the functions? I don't see one...

Hard Coded Limits

Edward G Nilges — Wed, 16 Aug 2006 21:13:19 -0700

To clarify Dan's comment, SQL Server ints can be up to 10 characters.

Also check out a related post, "Hard Coded Limits Considered Harmful," on my blog.

Edward

Cool!

Guest — Wed, 16 Aug 2006 06:34:56 -0700

Nice function. Thanks.

Length not issue for us

Mike — Fri, 04 Aug 2006 13:46:45 -0700

Our member numbers are all 6 characters, so I don't need to worry about anything larger. Infact our lowest numbers are still 3 digits so I could simply append 3 zeros to the leading edge.

I never considered creating a function, this is just a quick fix from our new database system to feed the web SQL box until we get the new system completely up and running.

Looks Like a Viable Solution

Daniel Read — Fri, 04 Aug 2006 13:43:11 -0700

I can't say whether or not this is particularly inefficient, but it looks like it should work fine as long as your company ID stays bellow 1,000,000. I believe a SQL Server int (> 0) can be up to 9 characters (?). Beyond that, though, I can't see anything wrong with it on the surface, especially if it were generalized into it's own function that took an int argument and returned a string. If you use this technique inline, it's less communicative than a function call would be.

Assuming you removed the limit on the 6 characters and generalized it into a function, it would be interesting to compare the two solutions for performance. Thanks for contributing this alternate solution.

Dan

Anything wrong with my method

Mike — Fri, 04 Aug 2006 13:05:36 -0700

I needed an int converted into a varchar of 6 characters with leading zeros. Add 6 zeros to the left of the converted int, then just take the 6 characters on the right.

RIGHT('000000' + CAST(dbo.Company.ID AS VARCHAR), 6) AS ID

Is this terribly ineffecient?

LPAD and RPAD

Guest — Mon, 03 Jul 2006 11:58:22 -0700

the RPAD and LPAD functions in SQL Server are very clever and appear to work perfectly. This is for non-commercial work, but I have given you credit in the code. Thanks!

scott
Prairie Heart Institute

thanks

Patrick — Fri, 16 Jun 2006 10:58:17 -0700

Excellent function. Worked like a charm.

Spaces - varchar issue?

Daniel Read — Fri, 12 May 2006 08:03:38 -0700

Hmmmm...could your right-padded spaces be disappearing because you're storing the string in a varchar variable? I believe right-padded spaces in a varchar are automatically removed. If this is the case, you might try storing your values in char variables/columns while processing your file export. Or have I misunderstood your issue?

Hope that helps,
Dan

RPAD with spaces is assumed as null

Caesar — Fri, 12 May 2006 07:21:24 -0700

I appreciate the effort in generating the function above. However, when trying to generate a fixed width file, one has to right pad alpha fields with spaces. Unfortunately, the spaces are treated like and are truncated when inserting into a field. Do you have a method to rpad spaces to an alpha field? I look forward to your feedback.

Caesar

Oh, sorry, there they are and they are kewl

Edward G Nilges — Sat, 07 Jan 2006 00:56:46 -0800

I do like the formality of saying, here is the function and it merits a change record.

Centering is another alignment task where you divide the difference between the length of a string and the (usually larger) length of a container by 2 and offset the string in the area by the quotient. You use the integer floor of the quotient when there is an odd number because n monospace spaces to the left and n+1 look more natural.

I coded an align routine which depending on a parameter will left, right or center justify a string within another string early in my VB addiction and this legacy code survives in the utilities.DLL for Build Your Own .Net Language and Compiler.

In Rexx I added the ability to justify code with even boundaries on either side. Unfortunately this, and to a degree centering, is at best workable "typesetting" only in the wonderful world of monospace fonts like Courier New, where all characters are of identical width, and you can make documents that are "beautiful" only in typewriter terms.

Such a low skill is useful still in programming when writing error reports, logs, and ransom notes while REAL computerised typesetting is vastly more complex.

Indeed, during my early addiction to VB I contemplated writing a typesetting package and little language exclusively for monospace but soon enough realized that this would be a Plan Nine from Outer Space.

However, I have written code to place messages in asterisk boxes.

Dig the New UDFs

Daniel Read — Fri, 06 Jan 2006 09:44:51 -0800

Your point about the "magic number" literal value of 10 is correct, Edward, but it was really just an example. My example was assuming a single SQL statement and I wanted to keep it simple, so I used the magic number. Unfortunately T-SQL does not support named constants explicitly, though you can approximate them with variables. Often, though, I solve the magic number problem in SQL with a single line comment above the line that uses the magic number to explain its meaning. If the code is within a T-SQL stored procedure, though, I'll create a pseudo-named-constant variable in place of the literal.

Also, in my post I suggested using branching logic rather than ABS() to handle the negative value possibility because after thinking about it I decided ABS() around the formula would assume too much about the intent of the caller by returning a string that is fewer characters than requested. My example does not contain the ABS() function because I wanted to leave that decision up to the coder to solve. I've added two user defined functions, and you can see there how I've decided to handle the negative problem. Someone else might prefer to use ABS() instead of the branching logic that I've implemented. Someone else might prefer to raise an error instead of just returning back the base string, as I have done in my UDFs.

Thanks for your interest,
Dan

Thanks, Donna

Daniel Read — Fri, 06 Jan 2006 09:35:52 -0800

I'll be doing a lot of SQL Server 2005 and SSIS work in the coming weeks, so I plan to continue blogging about it as I come up with things that might be helpful to other people. Have I recommended this excellent book yet for getting up to speed with SQL Server 2005? It's called Microsoft SQL Server 2005: Changing the Paradigm. It covers things from both the DBA and development perspectives, and has a very nice balance between giving high level strategic and tactical information about all the new stuff and getting into the weeds a little with some how-to and reference.

Dan

OK, but...

Edward G Nilges — Thu, 05 Jan 2006 23:16:06 -0800

The 10 is hard coded. If it is a parameter or even defined as a symbolic constant PAD_MAX which is probably good practice in the surrounding programming language, it becomes a teensy hard to "stuff" into the SQL and the SQL's intent is obscured thereby. Which makes it a shame that T-SQL doesn't implement a pad() function. Failing this, it would be nice, I think, if T-SQL had the general ability to define user functions.

If quantity is negative, the code doesn't work. Here you need an absolute value function (is this provided in your SQL?) AND a SQL version of the VB Iif (or the C ?: operator, a fun little bit of strangeness) if you are going to stay in one SQL call. If the call goes to the server, you need to.

As it is you are going to get 00000-12345 and other strange outputs if this is an order application and there are "returns".

It's enough to drive a man either to drink, or else to writing his Own Goddamn SQL (OGSQL). That's been done, of course, but by somebunny else.

Keep serving that SQL

Donna L Davis — Thu, 05 Jan 2006 18:16:01 -0800

Dan, I (for one) am appreciating your recent posts involving SQL Server. There's not a day that I don't go into Enterprise Manager (in the 2000 realm) and really need to wade into the 2005 waters.

SQL Server T-SQL LPAD & RPAD Functions (String Padding Equivalent to PadLeft & PadRight)

Daniel Read — Thu, 05 Jan 2006 11:31:59 -0800

Here is my method for achieving string padding in the Microsoft SQL Server T-SQL language. Unfortunately T-SQL does not offer functions like Oracle PL/SQL's LPAD() and RPAD() and C#'s PadLeft() and PadRight() functions. However, you can achieve the same thing using the T-SQL REPLICATE and LEN functions.