Remove spaces from a data field during a query?

[wjsullivan]

I've got a data field with all the states names (Illinois, New York, etc.). I need to use it in a query to build a URL, which requires me to be able to remove the spaces from the two word names.

Can I do this on the fly in my query? If so how?

Example of the delema:
SQL
'

https://www.'||STATENAME||'.com/xxxxxx'

AS 'URL'

Results
For Illinois it works great

http://www.illinois.com/xxxx

For New York it does not work at all

http://www.New

York.com/xxxx

How do I get rid of that space?

 

[SantaMufasa]

WJ,

First, in the Oracle/SQL*Plus World, your code will fail:

...'[URL unfurl="true"]https://www.'||STATENAME||'.com/xxxxxx'[/URL] AS 'URL'...

...because expression aliases (such as 'URL') must appear in double quotes: "URL".

You can achieve what you want by using either the Oracle "REPLACE" or Oracle "TRANSLATE" functions. I'll illustrate the code for both (when you want to "squeeze" certain characters out of a string, such as blank spaces in your case):

...'[URL unfurl="true"]https://www.'||replace(STATENAME,'[/URL] ',null)||'.com/xxxxxx' AS "URL"...

or

...'[URL unfurl="true"]https://www.'||translate(STATENAME,'x[/URL] ','x')||'.com/xxxxxx' AS "URL"...

Let us know your reactions to either of these methods.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]