很长,还没写完,今天晚上要接着写.
今天上午已把SQL写好,见下: --By 2005-06-28
建造函数的SQL:
create
function
CheckCharNum
--
检测字符串中有几个字符,返回查找到的数量
--
比如 select dbo.CheckCharNum('-','2002-1-1') 就会返回2
(
@findStr
varchar
(
10
),
@srcStr
varchar
(
30
)
)
returns
int
as
begin
declare
@num
int
declare
@site
int
set
@num
=
0
set
@site
=
charindex
(
@findStr
,
@srcStr
,
0
)
--
从开始找起
while
@site
>
0
begin
set
@num
=
@num
+
1
set
@site
=
charindex
(
@findStr
,
@srcStr
,
@site
+
1
)
end
return
@num
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create
function
CheckIsNum
--
检测一个字符串是不是一整数
(
@value
varchar
(
20
)
)
returns
int
as
begin
declare
@isNum
int
if
charindex
(
'
.
'
,
@value
)
=
0
and
isNumeric
(
@value
)
=
1
set
@isNum
=
1
else
set
@isNum
=
0
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
@isNum
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
1999..2.2
--
update pi_CZRKDJB set birth=replace(birth,'..','.') where charindex('..',birth)>0
--
1999.2.2 0:0
--
update pi_CZRKDJB set birth=replace(birth,' 0:0','') where charindex(' 0:0',birth)>0
--
字符长度为1
--
update pi_CZRKDJB set birth='' where len(birth)=1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符长度为2,建造函数
create
function
SetTwoChar
(
@datestr
varchar
(
2
)
)
returns
varchar
(
4
)
as
begin
declare
@retvalue
varchar
(
4
)
if
isNumeric
(
@datestr
)
=
1
begin
set
@retvalue
=
'
19
'
+
@datestr
+
'
-01-01
'
--
1999-01-01
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
else
begin
set
@retvalue
=
''
end
return
@retvalue
end
go
--
对字符长度为2的日期进行格式化.
--
update pi_CZRKDJB set birth=dbo.SetTwoChar(birth) where len(birth)=2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符长度为3,建造函数
create
function
SetThreeChar
(
@Datestr
varchar
(
3
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
charindex
(
'
.
'
,
@DateStr
)
>
0
--
如果是56.这种格式,则变为1956.01.01
begin
set
@formatdate
=
'
19
'
+
substring
(
@Datestr
,
1
,
2
)
+
'
-01-01
'
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
else
--
如果是35岁这种格式
begin
set
@formatdate
=
''
end
return
@formatdate
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
--
对字符长度为3的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetThreeChar(birth) where len(birth)=3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度为4,建造函数
create
function
SetFourChar
(
@Datestr
varchar
(
4
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
declare
@leftstr
varchar
(
4
)
declare
@rightstr
varchar
(
4
)
declare
@split
int
set
@formatdate
=
''
if
charindex
(
'
.
'
,
@DateStr
)
>
0
--
如果是56.1这种格式,则变为1956.01.01
begin
set
@split
=
charindex
(
'
.
'
,
@DateStr
)
set
@leftstr
=
substring
(
@DateStr
,
1
,
@split
-
1
)
set
@rightstr
=
substring
(
@DateStr
,
@split
+
1
,
len
(
@DateStr
))
if
dbo.CheckIsNum(
@leftstr
)
=
1
and
dbo.CheckIsNum(
@rightstr
)
=
1
begin
set
@formatdate
=
'
19
'
+
@leftstr
+
'
-0
'
+
@rightstr
+
'
-01
'
end
else
begin
set
@formatdate
=
''
end
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
else
--
如果是1956这种格式
begin
if
dbo.CheckIsNum(
@Datestr
)
=
1
set
@formatdate
=
@Datestr
+
'
-01-01
'
else
set
@formatdate
=
''
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
@formatdate
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为4的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetFourChar(birth) where len(birth)=4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度为5,建造函数
create
function
SetFiveChar
--
长度为5的格式有:61.11 ,47.3.,1956.,1927年,19885,199.7,61/11
(
@DateStr
varchar
(
5
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
charindex
(
'
.
'
,
@DateStr
)
>
0
--
如果有.的话,只可能位置在3与5是正确的
begin
if
charindex
(
'
.
'
,
@DateStr
)
=
3
--
'77.12'这种格式
set
@formatdate
=
'
19
'
+
substring
(
@DateStr
,
1
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
4
,
2
)
+
'
-01
'
else
if
charindex
(
'
.
'
,
@DateStr
)
=
5
--
1999.这种格式
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-01-01
'
else
set
@formatdate
=
''
end
else
begin
if
charindex
(
'
/
'
,
@DateStr
)
=
3
--
57/12这种格式
set
@formatdate
=
'
19
'
+
substring
(
@DateStr
,
1
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
4
,
2
)
+
'
-01
'
else
set
@formatdate
=
''
end
return
@formatdate
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
--对字符长度为5的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetFiveChar(birth) where len(birth)=5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度为6,建造函数
create
function
SetSixChar
--
长度为6的格式有:1960.9 , 98.4.6 ,1960-4 , 71/1/8, 1931/7 , 194702 ,1970。2
(
@DateStr
varchar
(
6
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
--
提出1960.1 1960-1 1960/1 这种格式
if
dbo.CheckCharNum(
'
-
'
,
@DateStr
)
=
1
or
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
1
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
1
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-0
'
+
substring
(
@DateStr
,
6
,
1
)
+
'
-01
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
提出48.2.1 48/2/1 48-2-1这种格式
else
begin
if
dbo.CheckCharNum(
'
-
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
2
set
@formatdate
=
'
19
'
+
substring
(
@DateStr
,
1
,
2
)
+
'
-0
'
+
substring
(
@DateStr
,
4
,
1
)
+
'
-0
'
+
substring
(
@DateStr
,
6
,
1
)
else
set
@formatdate
=
''
end
return
@formatdate
end
go
--
对字符长度为6的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetSixChar(birth) where len(birth)=6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度为7,建造函数
create
function
SetSevenChar
--
长度为7的格式有:1960.11 , 1955.02 , 57.11.7 ,64.1.22 ,1957-09 , 74/9/18,1997年9月, 1963/11
(
@DateStr
varchar
(
7
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
1
or
dbo.CheckCharNum(
'
-
'
,
@DateStr
)
=
1
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
1
--
如果是1960.11 1955.02 1960-11 1960/11这种格式
begin
if
dbo.CheckIsNum(
substring
(
@DateStr
,
1
,
4
))
=
1
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-
'
+
substring
(
@DateStr
,
6
,
2
)
+
'
-01
'
else
set
@formatdate
=
''
end
else
if
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
2
--
如果是51.7.17 , 51.11.7 51/11/2 这种格式
begin
if
dbo.CheckIsNum(
substring
(
@DateStr
,
4
,
2
))
=
1
--
表示51.11.7 51/11/7这种格式
set
@formatdate
=
'
19
'
+
substring
(
@DateStr
,
1
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
4
,
2
)
+
'
-0
'
+
substring
(
@DateStr
,
7
,
1
)
else
--
表示51.1.17 51/1/17这种格式
set
@formatdate
=
'
19
'
+
substring
(
@DateStr
,
1
,
2
)
+
'
-0
'
+
substring
(
@DateStr
,
4
,
1
)
+
'
-
'
+
substring
(
@Datestr
,
6
,
2
)
end
else
if
charindex
(
'
年
'
,
@DateStr
)
>
0
--
表示1969年2月
begin
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-0
'
+
substring
(
@DateStr
,
6
,
1
)
+
'
-01
'
end
else
set
@formatdate
=
''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
@formatdate
end
go
--
对字符长度为7的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetSevenChar(birth) where len(birth)=7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create
function
SetEightChar
--
长度为8的格式有:1931.6.9 78.11.20 1956-8-4 50/12/19 1950-5-6
(
@DateStr
varchar
(
8
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
dbo.CheckCharNum(
'
-
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
2
begin
if
dbo.CheckIsNum(
substring
(
@DateStr
,
1
,
4
))
=
1
--
如果是1940.7.6 1940/7/6 1940-7-6
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-0
'
+
substring
(
@DateStr
,
6
,
1
)
+
'
-0
'
+
substring
(
@DateStr
,
8
,
1
)
else
if
dbo.CheckIsNum(
substring
(
@DateStr
,
1
,
2
))
=
1
and
dbo.CheckIsNum(
substring
(
@DateStr
,
4
,
2
))
=
1
--
如果是40-12-30 40/12/30这种格式
set
@formatdate
=
'
19
'
+
substring
(
@DateStr
,
1
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
4
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
7
,
2
)
else
set
@formatdate
=
''
end
else
set
@formatdate
=
''
return
@formatdate
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为8的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetEightChar(birth) where len(birth)=8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度为9,建造函数
create
function
SetNineChar
--
长度为9的格式有:1931.12.9 1931.1.19 1931-12-9 1931-1-19 1950年1月9号 198510.31
(
@DateStr
varchar
(
9
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
dbo.CheckCharNum(
'
-
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
2
--
1931.12.9 1931.1.19 1931-12-9 1931-1-19
begin
if
dbo.CheckIsNum(
substring
(
@DateStr
,
1
,
4
))
=
1
begin
if
dbo.CheckIsNum(
substring
(
@DateStr
,
6
,
2
))
=
1
--
1931.12.9 1931-12-9 1931/12/9
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-
'
+
substring
(
@DateStr
,
6
,
2
)
+
'
-0
'
+
substring
(
@DateStr
,
9
,
1
)
else
if
dbo.CheckIsNum(
substring
(
@DateStr
,
8
,
2
))
=
1
--
1931.1.13 1931/1/13 1931-1-13
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-0
'
+
substring
(
@DateStr
,
6
,
1
)
+
'
-
'
+
substring
(
@DateStr
,
8
,
2
)
else
set
@formatdate
=
''
end
else
set
@formatdate
=
''
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
else
if
charindex
(
'
年
'
,
@DateStr
)
>
0
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-0
'
+
substring
(
@DateStr
,
6
,
1
)
+
'
-01
'
--
+substring(@DateStr,8,1)
else
set
@formatdate
=
''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
@formatdate
end
go
--
对字符长度为9的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetNineChar(birth) where len(birth)=9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度为10,建造函数
create
function
SetTenChar
--
长度为10的格式
(
@DateStr
varchar
(
10
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
dbo.CheckCharNum(
'
.
'
,
@DateStr
)
=
2
or
dbo.CheckCharNum(
'
/
'
,
@DateStr
)
=
2
--
1931.12.09 1931.01.19 1931/12/09 1931/01/19
begin
if
dbo.CheckIsNum(
substring
(
@DateStr
,
1
,
4
))
=
1
and
dbo.CheckIsNum(
substring
(
@DateStr
,
6
,
2
))
=
1
and
dbo.CheckIsNum(
substring
(
@DateStr
,
9
,
2
))
=
1
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-
'
+
substring
(
@DateStr
,
6
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
9
,
2
)
else
set
@formatdate
=
''
end
else
if
dbo.CheckCharNum(
'
-
'
,
@DateStr
)
=
2
set
@formatdate
=
@DateStr
else
set
@formatdate
=
''
return
@formatdate
end
go
--
对字符长度为10的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)=10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符串长度大于10,建造函数
create
function
SetBigTenChar
--
长度大于10的格式
(
@DateStr
varchar
(
30
)
)
returns
varchar
(
10
)
as
begin
declare
@formatdate
varchar
(
10
)
if
charindex
(
'
年
'
,
@DateStr
)
>
0
set
@formatdate
=
substring
(
@DateStr
,
1
,
4
)
+
'
-
'
+
substring
(
@DateStr
,
6
,
2
)
+
'
-
'
+
substring
(
@DateStr
,
9
,
2
)
else
set
@formatdate
=
''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
@formatdate
end
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度大于10的日期进行格式化
--
update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)>10
执行的SQL:
--
1999..2.2
update
pi_CZRKDJB
set
birth
=
replace
(birth,
'
..
'
,
'
.
'
)
where
charindex
(
'
..
'
,birth)
>
0
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
1999.2.2 0:0
update
pi_CZRKDJB
set
birth
=
replace
(birth,
'
0:0
'
,
''
)
where
charindex
(
'
0:0
'
,birth)
>
0
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
字符长度为1
update
pi_CZRKDJB
set
birth
=
''
where
len
(birth)
=
1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为2的日期进行格式化.
update
pi_CZRKDJB
set
birth
=
dbo.SetTwoChar(birth)
where
len
(birth)
=
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为3的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetThreeChar(birth)
where
len
(birth)
=
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为4的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetFourChar(birth)
where
len
(birth)
=
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
--对字符长度为5的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetFiveChar(birth)
where
len
(birth)
=
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为6的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetSixChar(birth)
where
len
(birth)
=
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为7的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetSevenChar(birth)
where
len
(birth)
=
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为8的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetEightChar(birth)
where
len
(birth)
=
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为9的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetNineChar(birth)
where
len
(birth)
=
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度为10的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetTenChar(birth)
where
len
(birth)
=
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
对字符长度大于10的日期进行格式化
update
pi_CZRKDJB
set
birth
=
dbo.SetTenChar(birth)
where
len
(birth)
>
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)