Problem solve Get help with specific problems with your technologies, process and projects.

Getting the length of a LONG data type and concatenating LONG with VARCHAR2

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

Dig Deeper on Oracle and SQL