CODEWITHSUNDEEP
sqloracle-databasereplace
BigRedEO
BigRedEO

Reputation: 847

REPLACE not doing what I need in SQL

I've got a query pulling data from a table. In one particular field, there are several cases where it is a zero, but I need the four digit location number. Here is where I'm running into a problem. I've got

SELECT REPLACE(locationNbr, '0', '1035') AS LOCATION...

Two issues -

  1. Whoever put the table together made all fields VARCHAR, hence the single quotes.
  2. In the cases where there already is the number 1035, I get 1103535 as the location number because it's replacing the zero in the middle of 1035.

How do I select the locationNbr field and leave it alone if it's anything other than zero (as a VARCHAR), but if it is zero, change it to 1035? Is there a way to somehow use TO_NUMBER within the REPLACE?

Upvotes: 1

Views: 62

Answers (3)

Gabriel Durac
Gabriel Durac

Reputation: 2770

SELECT CASE WHEN locationNbr='0' THEN '1035' ELSE locationNbr END AS LOCATION...

REPLACE( string, string_to_replace , replacement_string )

REPLACE looks for a string_to_replace inside a string and replaces it with a replacent_string. That is why you get the undesired behaviour - you are using the wrong function.

CASE WHEN condition THEN result1 ELSE result2 END CASE checks a condition and if it is true it returns result1 and if it is not it will return result2. This is a simple example, you can write a case statement with more than one condition check.

Upvotes: 2

Atif
Atif

Reputation: 2210

You can make use of length in Oracle:

select case when length(loacation) = 1 then REPLACE(loacation, '0', '1035') else loacation end as location
from location_test;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Don't use replace(). Use case:

(case when locationNbr = '0' then '1035' else locationNbr end)

Upvotes: 1

Related Questions