REPLACE not doing what I need in SQL

Question

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 -

Whoever put the table together made all fields VARCHAR, hence the single quotes.
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?

Gabriel Durac · Accepted Answer · 2020-08-20 15:19:13Z

2

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.

edited Aug 20, 2020 at 15:19

answered Aug 20, 2020 at 15:12

Gabriel Durac

2,7681 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-08-20 15:12:00Z

1

Don't use replace(). Use case:

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

answered Aug 20, 2020 at 15:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Atif · Accepted Answer · 2020-08-20 15:15:43Z

0

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;

answered Aug 20, 2020 at 15:15

Atif

2,2202 gold badges11 silver badges27 bronze badges

Collectives™ on Stack Overflow

REPLACE not doing what I need in SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related