SQL 기본 쿼리 (2) 숫자 관련 메서드: length(), concat(), left(), right(), split_part(), lpad(), rpad(), trim(), position(), repeat(), replace(), reverse(), 올림/내림/반올림/제곱근/ 절댓값/모듈러/랜덤

--length()

select length('hello world');

 

-- concat()

select concat('hello', ' ','world');

select 'hello' || ' ' || 'world' as "테스트";

 

-- left(), right()

select left ('SQL Class', 5), -- SQL C

right ('SQL Class', 4), -- lass

substring('SQL Class' from 2 for 3) -- QL

;

 

-- split_part() (String, separator, index (1부터 시작))

select split_part('서울시 양천구 신정동', ' ', 2); -- 양천구

 

-- lpad() (String, total place, placeholder)

select lpad('SQL', 10, '#'),

rpad('SQL', 5, '*');

 

 

-- trim()

select trim(both 'abc' from 'abcSQLababc'); -- SQL

select trim(leading 'abc' from 'abcSQLababc'); -- SQLababc

select trim(trailing 'abc' from 'abcSQLababc'); -- abcSQL

 

-- position()

select position('RUST' in 'SQL, JAVA, RUST'); -- 12

 

 

-- repeat()

select repeat('*', 5); -- *****

 

-- replace()

select replace('010.1234.5678', '.', '-'); -- 010-1234-5678

 

-- reverse()

select reverse('OLLEH');

 

 

-- number related methods

select ceil(123.17), floor(123.66), round(123.15);

 

select abs(-120), sign(-120), sign(120);

 

select 103 % 4, mod(103, 4);

 

select power(2,4), sqrt(16), round(random()*100);