How do you concatenate a 'long' column with a varchar2 column in Oracle? How do you get the lengths of the values stored in a 'long' column in Oracle?
To answer your first question you actually need to know the answer to the second question. To determine the length of the contents of a long datatyped column, simply use the LENGTH function. The LENGTH function returns an integer which is the total number of characters stored in that column. For instance, if I had a column that had the following phrase stored in it: 'Just do it!' If I used the LENGTH function to determine the number of characters (LENGTH(my_long_col)), I'd get back 11.
Now, once you have how many characters the long column contains, you can then concatenate the two columns as follows: my_varchar2_col || substr(my_long_col,1,length(my_long_col)). This works very nicely in PL/SQL.
I'll caution you that there may be instances where this won't work well (or at all) if the size of the data stored in the long column really is l-o-n-g (over 4000 bytes). In that case, you'll have to do a bit more work but it's still possible. If this is your situation, drop me another note back and I'll try to expand on this topic further. In the meantime, I hope this much helps!
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle 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, object-oriented and data warehousing gurus are waiting to answer your toughest questions.