SQL 髢「謨ーシ壽枚蟄鈴未謨ー

荳隕ァ

髢「謨ー 蜿門セ 邨先棡
UPPER 螟ァ譁蟄怜、画鋤 UPPER( 'aaa' ) 'AAA'
LOWER 蟆乗枚蟄怜、画鋤 LOWER( 'AAA' ) 'aaa'
INITCAP 蜈磯ュ螟ァ譁蟄怜、画鋤 INITCAP( 'aaa' ) 'Aaa'
LPAD 譁蟄怜沂繧(蟾ヲ蛛エ) LPAD( 'AAA' , 5 , '0' ) '00AAA'
RPAD 譁蟄怜沂繧(蜿ウ蛛エ) RPAD( 'AAA' , 5 , '0' ) 'AAA00'
SUBSTR 蛻繧雁コ縺 SUBSTR( 'ABC' , 2 , 2 ) 'BC'
TRIM 蛻繧頑昏縺ヲ TRIM( ' AA BB ' ) 'AA BB'
REPLACE 鄂ョ謠 REPLACE( 'ABC', 'B' , 'X' ) 'AXC'
CONCAT 謗・邯 CONCAT( 'AA' , 'BB' ) 'AABB'
LENGTH 譁蟄玲焚 LENGTH( 'AAA' ) 3
INSTR 譁蟄嶺ス咲スョ INSTR( 'ABC' , 'A' ) 1
螟ァ譁蟄/蟆乗枚蟄

蟆乗枚蟄怜、画鋤
LOWER('ABC DEF')
竍'abc def'

螟ァ譁蟄怜、画鋤
UPPER('abc def')
竍'ABC DEF'

蜈磯ュ譁蟄励ョ縺ソ螟ァ譁蟄怜、画鋤
INITCAP('ABC DEF')
竍'Abc Def'
INITCAP('abc def')
竍'Abc Def'

シ仙沂繧

謖螳壽枚蟄怜沂繧(蜿ウ蟇縺)
LPAD('ABC', 5, '*')
竍'**ABC'

謖螳壽枚蟄怜沂繧(蟾ヲ蟇縺)
RPAD('ABC', 5, '*')
竍'ABC**'

蛻怜錐縺ォシ疲。√ョ譎ょ綾縺後≠繧句エ蜷(萓具シ0545)
縺薙l縺栗NTEGER蝙九□縺ィ縲545縺ィ縺ェ繧九
縺薙ョ蝣エ蜷医シ仙沂繧√ッ荳頑焔縺剰。後°縺ェ縺縲ゅ

SELECT RIGHT('0000'+ 蛻怜錐, 2) FROM [TABLE]

豁」縺励¥縺ッ縲
SELECT RIGHT('0000'+ CONVERT(VARCHAR,蛻怜錐), 2) FROM [TABLE]

蛻繧雁コ縺

窶サシカシ「縺ァ險縺Mid()髢「謨ー

蛻繧雁コ縺
SUBSTR('ABCDEF' ,1 ,3)
竍'ABC'

SUBSTR('ABCDEF' ,-3)
竍'DEF'

select
縲col1,
col1蛻励ョ3譁蟄礼岼縺九i1譁蟄怜繧雁叙繧
substring(col1 from 3 for 1)
col1縺梧焚蛟、蝙九ョ蝣エ蜷
縲substring('' || col1 from 3 for 1)
from
縲ス

SUBSTRING
SUBSTRING( expression, start, length )

SELECT SUBSTRING( 蛻怜錐, 1, 2) FROM [TABLE]

LEFT
SELECT LEFT(蛻怜錐,2) FROM [TABLE]

窶サVB縺ィ蜷後§菴ソ縺譁ケ

RIGHT
SELECT RIGHT(蛻怜錐,2) FROM [TABLE]

窶サVB縺ィ蜷後§菴ソ縺譁ケ
DB縺ョ遞ョ鬘槭↓繧医▲縺ヲ縺ッ菴ソ縺医↑縺(Oracle遲)縲

蛻繧頑昏縺ヲ

蜑榊セ後ョ蜊願ァ偵せ繝壹シ繧ケ蜑企勁
TRIM(' abc def ')
竍'abc def'
窶サ逵溘s荳ュ縺ョ蜊願ァ偵せ繝壹シ繧ケ縺ッ蜑企勁縺輔l縺ェ縺縲ょィ隗偵せ繝壹シ繧ケ縺ッ蜑榊セ後↓縺ゅ▲縺ヲ繧ら┌隕悶

蜑榊セ後ョ謖螳壽枚蟄怜炎髯、
TRIM('G' FROM 'GoodMorning')
竍'oodMorning'

TRIM(' ' FROM ' abc def ')
竍'abc def' 窶サTRIM(' abc def ')縺ィ蜷後§

BOTH/LEADING/TRAINLING 窶サ逵∫払蜿ッ(BOTH縺後ョ繝輔か繝ォ繝)

TRIM(BOTH ' ' FROM ' abc def ')
竍'abc def' 窶サTRIM(' abc def ')縺ィ蜷後§

TRIM(LEADING ' ' FROM ' abc def ')
竍'abc def '

TRIM(TRAILING ' ' FROM ' abc def ')
竍' abc def'

trim(' aa a ')
竊誕a a
繝医Μ繝縺ッ蜑榊セ後ョ縺ソ
騾比クュ繧偵ヨ繝ェ繝縺吶k蝣エ蜷医ッ鄂ョ謠繧剃スソ逕ィ
鄂ョ謠

REPLACE('Good', 'G', 'W')
竍'Wood'
豁」隕剰。ィ迴セ縺ォ繧医k鄂ョ謠
regexp_replace( 蟇セ雎。, 讀懃エ「, 鄂ョ謠 [,郢ー繧願ソ斐@])
regexp_replace( ' a縲a a ', '(\s|縲)', '', 'g')
竊誕aa
謗・邯

CONCAT('ABC' , 'DEF')
竍'ABCDEF'

竊 繧ょ庄
'ABC'縲||縲'DEF'
竍'ABCDEF'

譁蟄玲焚

LENGTH('ABC')
竍3

LENGTH('ABC')
竍3

CHAR_LENGTH('ABC')
CHARACTER_LENGTH('ABC')
繧ょ庄(蜷梧ゥ溯ス)

譁蟄嶺ス咲スョ

INSTR('ABC', 'A')
竍1
窶サ1縺九i

INSTR('ABC', 'Z')
竍0
窶サ隧イ蠖鍋┌縺

Follow me!

谺。縺ョ險倅コ

SQL 髢「謨ーシ壽律莉倬未謨ー