How do I concatenate fields together but keep their true field lengths? I am trying to join three varchar2 length(2)fields together but need to keep the spaces in these fields to create a 5 item code.
In Oracle, you need to dynamically determine the length of the string and pad it with spaces. Here's how it would look:
SELECT FirstString || RPAD(' ',2-NVL(LENGTH(FirstString),0)) || SecondString || RPAD(' ',2-NVL(LENGTH(SecondString),0)) || ThirdString || RPAD(' ',2-NVL(LENGTH(ThirdString),0)) FROM YourTable
In SQL Server, you could do it one of two ways. This is simply a rewrite of the Oracle method using SQL Server specific functions:
SELECT FirstString + REPLICATE(' ',2-DATALENGTH(FirstString)) + SecondString + REPLICATE(' ',2-DATALENGTH(SecondString)) + ThirdString + REPLICATE(' ',2-DATALENGTH(ThirdString)) FROM YourTable
The other way, which I prefer, casts each column to a fixed length column:
SELECT CAST (FirstString as CHAR(2)) + CAST (SecondString as CHAR(2)) + CAST (ThirdString as CHAR(2)) FROM YourTable
For More Information
- What do you think about this answer? E-mail the Edtior at firstname.lastname@example.org with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an Oracle, SQL Server, or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle, SQL Server, and SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.