我想知道如何编写一个SQL自定义函数来把性别判读出来,我找了一个这样功能的过程存储,但是我需要函数,请高手指点,或则帮我把这个过程存储转换成函数
create proc get_date_sex(@s varchar(18))
as
select
case len(@s)
when 15 then '19'+substring(@s,7,2)+'-'+substring(@s,9,2)+'-'+substring(@s,11,2)
when 18 then substring(@s,7,4)+'-'+substring(@s,11,2)+'-'+substring(@s,13,2)
end as 出生日期,
case (case len(@s)
when 15 then substring(@s,14,1)
when 18 then substring(@s,16,1)
end)%2
when 1 then '男' else '女'
end as 性别
go
create proc get_date_sex(@s varchar(18))
as
select
case len(@s)
when 15 then '19'+substring(@s,7,2)+'-'+substring(@s,9,2)+'-'+substring(@s,11,2)
when 18 then substring(@s,7,4)+'-'+substring(@s,11,2)+'-'+substring(@s,13,2)
end as 出生日期,
case (case len(@s)
when 15 then substring(@s,14,1)
when 18 then substring(@s,16,1)
end)%2
when 1 then '男' else '女'
end as 性别
go
returns bit
as
begin
return(
case (case len(@s)
when 15 then substring(@s,14,1)
when 18 then substring(@s,17,1)
end)%2
when 1 then 1 else 0 --返回1是男,0是女
end
)
end
go
create table tb(id varchar(18))
insert into tb values('111111111111111111')
insert into tb values('222222222222222222')
insert into tb values('333333333333333')
insert into tb values('444444444444444')
goselect id,
case
when len(id) = 18 and cast(substring(id,17,1) as int) % 2 = 1 then '男'
when len(id) = 18 and cast(substring(id,17,1) as int) % 2 = 0 then '女'
when len(id) = 15 and cast(substring(id,14,1) as int) % 2 = 1 then '男'
when len(id) = 15 and cast(substring(id,14,1) as int) % 2 = 0 then '女'
end as sex
from tbdrop table tb/*
id sex
------------------ ----
111111111111111111 男
222222222222222222 女
333333333333333 男
444444444444444 女(所影响的行数为 4 行)
*/
create function dbo.get_date_sex(@s varchar(18))
returns bit
as
begin
return(
case (case len(@s)
when 15 then substring(@s,15,1) --这里是最后一位
when 18 then substring(@s,17,1) --这里是倒数第二
end)%2
when 1 then 1 else 0 --返回1是男,0是女
end
)
end
go
create function dbo.get_sex(@s varchar(18))
returns nvarchar(2)
as
begin
return
(
case (case len(@s) when 15 then substring(@s,15,1) when 18 then substring(@s,17,1) end)%2
when 1 then '男' else '女' end
)
end
CREATE FUNCTION get_date_sex (@s varchar(18),@t varchar(4))
RETURNS varchar(10) AS
BEGIN
if len(@s) = '18'
begin
if @t = '生日'
begin
set @s = substring(@s,7,4)+'-'+substring(@s,11,2)+'-'+substring(@s,13,2)
end
if @t = '性别'
begin
set @s = substring(@s,17,1) % 2
if @s = '1'
begin
set @s = '男'
end
else
begin
set @s = '女'
end
end
end
if len(@s) = '15'
begin
if @t = '生日'
begin
set @s = '19'+substring(@s,7,2)+'-'+substring(@s,9,2)+'-'+substring(@s,11,2)
end
if @t = '性别'
begin
set @s = substring(@s,15,1) % 2
if @s = '1'
begin
set @s = '男'
end
else
begin
set @s = '女'
end
end
end
return @s
ENDgoselect *,dbo.get_date_sex(id,'生日') as 生日,dbo.get_date_sex(id,'性别') as 性别 from T1drop function get_date_sex
goCREATE FUNCTION get_date_sex (@s varchar(18),@t varchar(4))
RETURNS varchar(10) AS
BEGIN
if len(@s) = '18'
begin
if @t = '生日'
begin
set @s = substring(@s,7,4)+'-'+substring(@s,11,2)+'-'+substring(@s,13,2)
end
if @t = '性别'
begin
set @s = substring(@s,17,1) % 2
if @s = '1'
begin
set @s = '男'
end
else
begin
set @s = '女'
end
end
end
if len(@s) = '15'
begin
if @t = '生日'
begin
set @s = '19'+substring(@s,7,2)+'-'+substring(@s,9,2)+'-'+substring(@s,11,2)
end
if @t = '性别'
begin
set @s = substring(@s,15,1) % 2
if @s = '1'
begin
set @s = '男'
end
else
begin
set @s = '女'
end
end
end
return @s
END go select *,dbo.get_date_sex(id,'生日') as 生日,dbo.get_date_sex(id,'性别') as 性别 from T1 drop function get_date_sex
goCREATE FUNCTION get_date_sex (@s varchar(18),@t varchar(4))
RETURNS varchar(10) AS
BEGIN
if len(@s) = '18'
begin
if @t = '生日'
begin
set @s = substring(@s,7,4)+'-'+substring(@s,11,2)+'-'+substring(@s,13,2)
end
if @t = '性别'
begin
set @s = substring(@s,17,1) % 2
if @s = '1'
begin
set @s = '男'
end
else
begin
set @s = '女'
end
end
end
if len(@s) = '15'
begin
if @t = '生日'
begin
set @s = '19'+substring(@s,7,2)+'-'+substring(@s,9,2)+'-'+substring(@s,11,2)
end
if @t = '性别'
begin
set @s = substring(@s,15,1) % 2
if @s = '1'
begin
set @s = '男'
end
else
begin
set @s = '女'
end
end
end
return @s
END go select *,dbo.get_date_sex(id,'生日') as 生日,dbo.get_date_sex(id,'性别') as 性别 from T1 drop function get_date_sex