トップページに戻る    次のSQLServerのサンプルへ    前のSQLServerのサンプルへ

1-2 文字列関数(文字列の加工など)


select Len('1234') as "Len関数1",
Len('') as "Len関数2",
Len(null) as "Len関数3",
Len('a b     ') as "Len関数4",
go

Len関数1  Len関数2   Len関数3  Len関数4
--------  --------  --------  --------
       4         0      NULL         3

MSDN --- Len


select DataLength('あいう') as "DataLength関数1",
DataLength('123') as "DataLength関数2",
DataLength('あいA') as "DataLength関数3"
go

DataLength関数1  DataLength関数2  DataLength関数3
---------------  ---------------  ---------------
              6                3                5

MSDN --- DataLength


select Left('1234',2) as "Left関数",
Right('1234',2) as "Right関数",
SubString('123456789',3,1) as "SubString関数1",
SubString('123456789',3,9999) as "SubString関数2"
go

Left関数  Right関数   SubString関数  SubString関数2
--------  ---------  -------------  --------------
12        34         3              3456789

MSDN --- Left
MSDN --- Right
MSDN --- SubString


select
cast(Replace(LTrim('  ABC  '),' ','★') as varchar(10)) as "LTrim関数",
cast(Replace(RTrim('  ABC  '),' ','★') as varchar(10)) as "RTrim関数",
cast(Replace(Space(5),' ','★') as varchar(10)) as "Space関数"
go

LTrim関数  RTrim関数  Space関数
---------  ---------  ----------
ABC★★    ★★ABC    ★★★★★

MSDN --- LTrim
MSDN --- RTrim


select cast(Replace('abcabcabc','A','x') as varchar(10)) as "Replace関数1",
cast(Replace('abcabcabc','A','') as varchar(10)) as "Replace関数2",
Reverse('123456789') as "Reverse関数"
go

Replace関数1  Replace関数2   Reverse関数
------------  ------------  ------------
xbcxbcxbc     bcbcbc        987654321

MSDN --- Replace
MSDN --- Reverse


select Replicate('A',3) as "Replicate関数",
Stuff('abcde',2,3,'X') as "Stuff関数1",
Stuff('abcde',2,3,'') as "Stuff関数2"
go

Replicate関数  Stuff関数1   Stuff関数2
-------------  ----------  --------
AAA            aXe         ae

MSDN --- Replicate
MSDN --- Stuff