alter function GetStr( @num bigint)
returns varchar(1000)
as
begin
declare @str varchar(100)
SELECT @STR=''
declare @str_RESULT varchar(100)
SELECT @str_RESULT=''
declare @index int
select @index=len(@num)
declare @num_left varchar(100)
declare @num_right varchar(100)
while(@index>3)
begin
select @num_left=left(@num,3)
select @num_right=RIGHT(@num,len(@num)-3)
select @num_left=@num_left+'.'
select @str=@str_RESULT+@num_left
SELECT @str_RESULT=@str_RESULT+@num_left
select @index=len(@num_right)
select @num=@num_right
if(@index>3)
BEGIN
continue
END
else
BEGIN
SELECT @str=@str+@num_right
break
END
end
RETURN @str
end select [dbo].GetStr(12345670980002)
/*
123.456.709.800.02
/*
returns varchar(1000)
as
begin
declare @str varchar(100)
SELECT @STR=''
declare @str_RESULT varchar(100)
SELECT @str_RESULT=''
declare @index int
select @index=len(@num)
declare @num_left varchar(100)
declare @num_right varchar(100)
while(@index>3)
begin
select @num_left=left(@num,3)
select @num_right=RIGHT(@num,len(@num)-3)
select @num_left=@num_left+'.'
select @str=@str_RESULT+@num_left
SELECT @str_RESULT=@str_RESULT+@num_left
select @index=len(@num_right)
select @num=@num_right
if(@index>3)
BEGIN
continue
END
else
BEGIN
SELECT @str=@str+@num_right
break
END
end
RETURN @str
end select [dbo].GetStr(12345670980002)
/*
123.456.709.800.02
/*
解决方案 »
- 大家来看看这个语句要怎么写呢?
- 简单 sql 脚本插入问题 !!
- 如何将两表数据合并,忘各位帮忙,急....
- Sql登录,我为什么进不去???密码没错呀!
- 树人 xys_777
- 关于如何把datetime类型的字符转变成字符串类型的问题~~
- 多进程和多线程对表的读写访问需要加锁吗
- 各位大哥,update无法更新字段,为什么啊?
- 怎样才能让数据库只允许限定的ip进行odbc或是oledb的连接?
- ?????sql server 7.0 的数据库日志过大怎么办?????
- 请教在调用存储过程时报"从字符串向 datetime 转换时失败"的错误如何解决?
- 请教调用存储过程时报"从字符串向 datetime 转换时失败"的错误?
select rtrim(no)+'.'
from (
select rn,substring(no,rn*3-2,3) as no
from (select N'12345455666767' as no) a
left join (select (select count(*)
from sysobjects
where id>=b.id
) as rn
from sysobjects b
) c on ceiling(len(a.no)/3.0)>=c.rn
) d
order by rn
for xml path('')-- for sql server 2005