Check to see if field has a number

[fmrock]

Right now in my select I am doing displaying a field.

I.INSNAME "Payer_Name",

However, I have been told that I need to check the I.INSADDR1 field and if it does not contain a number then add it to the payer name column.

I.INSADDR1 || ' ' || I.INSNAME "Payer_Name",

 

[fmrock]

I was able to do this, but I am guessing there is a better way or maybe some with less code.

	CASE
	  WHEN I.INSADDR1 LIKE '%0%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%1%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%2%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%3%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%4%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%5%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%6%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%7%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%8%' THEN I.INSNAME
	  WHEN I.INSADDR1 LIKE '%9%' THEN I.INSNAME
	  ELSE TRIM(I.INSADDR1 || ' ' || I.INSNAME)
	END	"Payer_Name",

 

[smbpalepilsen]

do it in for loop

 

[Thargy]

fmrock,

like is extremely expensive, use INSTR instead to detect the presence of a digit. I believe that there is a more efficient way still (maybe using translate) but I'll have to think about it.

I do not believe that a PL/SQL loop will be the most efficient way to achieve what you want.

Regards

T

 

[sem]

Don't forget about translate() function, e.g.

case
         when translate(I.INSADDR1, '.1234567890', '.') = I.INSADDR1 then
          TRIM(I.INSADDR1 || ' ' || I.INSNAME)
         else
          I.INSNAME
end

Using owa_pattern is more readable but less efficient.

If you're lucky to be on 10g+ try REGEXP_LIKE (didn't compare for efficiency yet)

Regards, Dima

http://seminihin.narod.ru