Using a SQL function to pad strings with spaces

Question

Is it inefficient to use a user defined function to pad spaces? I have a padding function that I'd more intuitive than using the built in REPLICATE function but I am afraid it is introducing inefficiency into the code.

The padding must be done in SQL.

I assume you are left padding. Right padding is trivial, because you can just cast the value to a char(). — Gordon Linoff
– Gordon Linoff, Commented Apr 25, 2015 at 15:21
Thanks. What we have is a lot of calls to user defined functions for right and left padding already written to do this and I'm wondering if it is worth replacing it with the suggestions above. — a b
– a b, Commented Apr 25, 2015 at 16:14

Manngo · Accepted Answer · 2022-07-15 07:00:33Z

4

You can use RIGHT or LEFT depending on the padding direction.
For example:

SELECT RIGHT('11111' + originalString, 5)

This will pad your string with on the left with 1s to make a 5 letters string. (I've used 1s instead of spaces so it will be easy to read. For spaces you can use the SPACE function:

SELECT RIGHT(SPACE(5) + OriginlString, 5)

to pad to the right you can do this:

SELECT LEFT(OriginalString + SPACE(5), 5)

or simply convert to char as suggested by Gordon Linoff in the comments

edited Jul 15, 2022 at 7:00

Manngo

17k13 gold badges109 silver badges153 bronze badges

answered Apr 25, 2015 at 15:08

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

a b Over a year ago

Thanks. What we have is a lot of calls to user defined functions for right and left padding already written to do this and I'm wondering if it is worth replacing it with the suggestions above.

Zohar Peled Over a year ago

You shold know better then anyone since you know you system best. It's probably possible to replace one udf call ant test performance, and then decide what to do.

Collectives™ on Stack Overflow

Using a SQL function to pad strings with spaces

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related