I see in some documentation on the web that there is an "LPAD" function: LPAD(string, length, pad), RPAD(string, length, pad) Returns string padded on left or right to length characters using the pad string as padding. Pad may be omitted: defaults to single space. I do not see that this exists in SQL Server, does anyone know how I can do this in SQL Server without having to use a ridiculous case statement? I have an incoming field that is 2, 3, 4, or up characters and I want to deliver this to a field with size of 12, with the data "right-justified" and padded will spaces or nulls at the beginning. For example: incoming Data: "ABC" outgoing data: " ABC" Thanks. Rodney
You don't need a CASE expression for this DECLARE @a VARCHAR(3) SET @a = 'ABC' SELECT RIGHT(REPLICATE(' ',10) + @a ,10) AS RPAD , LEFT(@a + REPLICATE(' ',10) ,10) AS LPAD RPAD LPAD ---------- ---------- ABC ABC (1 row(s) affected) -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Frank, As always, thank you very much. I really appreciate the rapid turn around and the quick solution. It worked like a charm! I didn't know about the REPLICATE statement. I searched through books online for "pad" to no avail. Thanks! Rodney
