sql subquery resulting the unwanted braces with result

Question

i am having big query where i am using one below subquery but that is returning unwanted braces ...

SUBSTR(work__note.note_text,1,10000) || '  ' ||  (select user_name from cm_use where SEQ_USER_ID in work_request_note.create_user_id) || '   ' || work__note.create_date   "Notes"

this is giving below output :

"Work was assigned to Kristie Cut (ard1) 01-NOV-23"

desired output : "Work was assigned to Kristie Cutt klcd1 01-NOV-23"

SUBSTR(work_note.note_text,1,10000) || ' ' || (select user_name from cm_use where SEQ_USER_ID in work__note.create_user_id) || ' ' || work_note.create_date "Notes"

this is giving below output :

"Work Request was assigned to Kri Cut (kld1)   01-NOV-23"

desired output : "Work  was assigned to Kris Cup kld1   01-NOV-23"

Answer 1

You could use Replace function to get ridd of braces. If your result is like in the question you can do it on resulting string:

Select    Replace(  Replace('Work Request was assigned to Kristie Cupitt (klccard1)   01-NOV-23', 
                            '(', ''
                           ), 
                  ')', ''
                 ) "TXT"
From Dual 

--  TXT                                                             
--  ----------------------------------------------------------------
--  Work Request was assigned to Kristie Cupitt klccard1   01-NOV-23

... Or (better) on column that containes braces. In this sample I would say that it is USER_NAME from table CMS_USER. If that is so, then your subquery, changed like below, should give you expected result.

(select Replace(Replace(user_name, '(', ''), ')', '') "USER_NAME" from cms_user where SEQ_USER_ID in work_request_note.create_user_id)

BTW: your SUBSTR(work_request_note.note_text,1,10000) part does not make sense since SubStr is character function and columns of that type could have max length of 4000 so if you want the whole text than there is no need for SubStr at all. You could just put work_request_note.note_text instead. On the other hand if you ommit the last parameter then you will get the result untill the end of string whatever length it could be...