Home » SQL & PL/SQL » SQL & PL/SQL » Any ideas how to concatenate two fields depending on length? (10G)
Any ideas how to concatenate two fields depending on length? [message #329034] Mon, 23 June 2008 17:15 Go to next message
radalapsker@yahoo.com
Messages: 3
Registered: March 2008
Junior Member
Hello

I need to get user location where the city may be cut off so as to maintain a max location length of 40 characters.

For example, if city + country fit:

user_location := city || ', ' || country ;
# Result: Los Angeles, California 


But if city + country don't fit:

user_location := substr(city,1,?) || '..., ' || country ;
# Result: Sampedro De Macor..., Dominican Republic


Is it possible to do in a single sql statement? Thanks for any help!
Re: Any ideas how to concatenate two fields depending on length? [message #329035 is a reply to message #329034] Mon, 23 June 2008 18:17 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Yes, lookup using a combination of CASE and LENGTH for this query. You should be able to work out the logic from there.
Re: Any ideas how to concatenate two fields depending on length? [message #329041 is a reply to message #329034] Mon, 23 June 2008 21:26 Go to previous messageGo to next message
leuphis
Messages: 2
Registered: June 2008
Location: Guangzhou City GuangDong ...
Junior Member

select book_code,decode(sign(length(book_code)-10),'-1',book_code,substr(book_code,0,10)||'....') from book

may be this will help you
Re: Any ideas how to concatenate two fields depending on length? [message #329057 is a reply to message #329041] Mon, 23 June 2008 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@leuphis,

2 remarks:
- sign returns a number, '-1' is a string
- character index in string starts at 1 not 0

Also, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Any ideas how to concatenate two fields depending on length? [message #329257 is a reply to message #329057] Tue, 24 June 2008 13:55 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Hope this would help.



with tab as (      
        select 'San Jose Belmonte' city
                ,'United States Of America' cty
                ,40 as len
        from dual 
      )
select  city
       , cty        
       ,case 
        when ( length(city) + length(cty) + length(', ') ) > len then
            substr( city, 1, ( len -  length(cty) ) - length('..., ')  ) || '..., ' || cty
        else
            city || ', ' || cty
        end as lbl
from tab



Regards,
Rhani
Previous Topic: Cursor_sharing=similar
Next Topic: data_type as condition
Goto Forum:
  


Current Time: Fri Dec 02 12:45:24 CST 2016

Total time taken to generate the page: 0.09924 seconds