본문 바로가기

IT/Firebird

firebird replace문

REPLACE()


Syntax: 

REPLACE (str, find, repl)


Examples: 

replace ('Billy Wilder',  'il', 'oog')     -- returns 'Boogly Woogder'

replace ('Billy Wilder',  'il',    '')     -- returns 'Bly Wder'

replace ('Billy Wilder',  null, 'oog')     -- returns NULL

replace ('Billy Wilder',  'il',  null)     -- returns NULL

replace ('Billy Wilder', 'xyz',  null)     -- returns NULL (!)

replace ('Billy Wilder', 'xyz', 'abc')     -- returns 'Billy Wilder'    

replace ('Billy Wilder',    '', 'abc')     -- returns 'Billy Wilder'




ex) 공백 2개를 1개로 UPDATE


1. 칼럼에서 공백2개 조회

 => select dcdnm, replace(dcdnm,'  ',' ') new_nm from dcode where dcdnm like '%  %'


2. UPDATE

 => update dcode

        set dcdnm = replace(dcdnm,'  ',' ')

      where dcdnm like '%  %'