Oracle string concatenation
Posted on 2009-07-15
I have 3 strings I want to concatenate, they exceed 4000 together:
decode(trim(C.TEST_DESC) || trim(C.KEY_ITMS) || trim(C.TEST_EXCPTN), null, 'No',
the first is 1951, second is 586, third is 2017 for a total of 4554
this of course works, which seriously, is all I need:
decode(substr(substr(C.TEST_DESC,1,5) || substr(C.KEY_ITMS,1,5) || substr(C.TEST_EXCPTN,1,5),1,5), null, 'No',
Because all I'm checking is if the first sets are all null or not...
But I want to understand this... So please help me...
decode(substr(substr(C.TEST_DESC,1,4000) || substr(C.KEY_ITMS,1,4000) || substr(C.TEST_EXCPTN,1,4000),1,4000)
So does this:
decode(substr(trim(C.TEST_DESC) || trim(C.KEY_ITMS) || trim(C.TEST_EXCPTN),1,5), null, 'No',
Shouldn't the outside substr be enough? Taking the final string and making sure it is 4000? But it doesn't. Even with an addtional subtr wrap on all the columns, it still breaks.
What would one do with this if one reallly wanted to return 4000 characters of all of them concatenated?
Because I do have existing SP's that use this type of concatenation everywhere so I want to make certain I can fix any further issues.