您的位置：程序门 -> ms-sql server -> 应用实例

数据转换求助，高手请进，在线等

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

数据转换求助，高手请进，在线等[已结贴,结贴人:shiguangfeishi]

发表于：2007-06-14 11:46:12 楼主

select web_customerid from customerdata
在web_customerid字段中有如下数据（排除前后多余的0之后再取6位数字
0000213612
0000212628
2185790000
2187760000
0002126310
0002126330
结果
213612
212628
218579
218776
212631
212633

sql如何写，在线等

发表于：2007-06-14 11:47:331楼得分:10

select replace（web_customerid, "0 ", " ") as web_customerid from customerdata

发表于：2007-06-14 11:49:332楼得分:0

全部都是数字吗?

发表于：2007-06-14 11:50:453楼得分:50

如果只有 0-9 这类的数字, 则可以用:

select reverse（right（convert（bigint, reverse（convert（bigint, web_customerid))), 5))
from customerdata

发表于：2007-06-14 11:51:224楼得分:0

-- 6 位
select reverse（right（convert（bigint, reverse（convert（bigint, web_customerid))), 6))
from customerdata

1楼把中间的0都替换掉了

发表于：2007-06-14 11:51:525楼得分:0

上面的兄弟这样不行，会把中间的零也替换掉

发表于：2007-06-14 11:54:236楼得分:15

create table t1（c varchar（20))
insert t1
select '0000213612 '
union all select '0000212628 '
union all select '2185790000 '
union all select '2187760000 '
union all select '0002126310 '
union all select '0002126330 '

go
select c=left（reverse（cast（reverse（cast（c as bigint)) as bigint)),6) from t1
go
drop table t1

/*
c
------------------------
213612
212628
218579
218776
212631
212633
*/

发表于：2007-06-14 11:58:437楼得分:0

现在遇到问题了，把有些正常的6位如：321350的最后一个0也排除了，
有什么方法只将最前面的0排除，然后取6位？

发表于：2007-06-14 11:59:378楼得分:0

如果可以再讲讲这些方法及思路，谢谢

发表于：2007-06-14 11:59:569楼得分:0

select right（convert（bigint, web_customerid), 6)
from customerdata

发表于：2007-06-14 12:01:1910楼得分:0

排除前后多余的0
-----------------------------

你最原始的要求是删除前后多余的0 , 所以不能怪大家写错.

只要去掉前面的0, 则直接将数据转换为数字, 前面就没有0的, 再取 6 位就ok, 这个过程自动转换回字符

发表于：2007-06-14 15:19:0711楼得分:0

update customerdata set
refid_temp=b.srd_customerid
from
（
select srd_customerid,
convert（int,left（convert（bigint, ltrim（rtrim（web_customerid))), 6)) as web_customerid
from srd_customerdata
where web_customerid is not null
and web_customerid <> ' '
and isnumeric（web_customerid)=1
and isnumeric（srd_customerid)=1
)
b where
customerdata.customerid=b.web_customerid

报错：从数据类型 varchar 转换为 bigint 时出错。
如何将一些垃圾数据也处理掉？

发表于：2007-06-14 15:41:5112楼得分:15

参考leo_lesley（leo) （ )的方法，写一下：
create table t1（c varchar（20))
insert t1
select '0000213612 '
union all select '0000212628 '
union all select '2185790000 '
union all select '2187760000 '
union all select '0002126310 '
union all select '0002126330 '

go
--select c=left（reverse（cast（reverse（cast（c as bigint)) as bigint)),6) from t1
--中间出现0的就错了
--修改后方法：
select cast（reverse（cast（cast（reverse（c) as bigint) as nvarchar（50))) as bigint) from t1
go
drop table t1

/*
c
------------------------
213612
212628
218579
218776
212631
212633
*/

发表于：2007-06-14 15:46:2713楼得分:0

翻转2次并cast（x as bigint)就可以,提前就是提供的数据一定要求为可以转换为数字的数值。
不然就出现类型转换错误。
要含有字母等其他特殊字符，得另考虑，可能只有自定义函数解决。

发表于：2007-06-14 16:09:0314楼得分:10

select replace（web_customerid, '0 ', ' ') web_customerid from customerdata
返回：
web_customerid
-------------------
213612
212628
218579
218776
212631
212633

（所影响的行数为 6 行）

发表于：2007-06-14 16:10:4415楼得分:0

即：
create table customerdata（web_customerid varchar（30))
insert customerdata values（ '0000213612 ')
insert customerdata values（ '0000212628 ')
insert customerdata values（ '2185790000 ')
insert customerdata values（ '2187760000 ')
insert customerdata values（ '0002126310 ')
insert customerdata values（ '0002126330 ')
select * from customerdata
select replace（web_customerid, '0 ', ' ') web_customerid from customerdata
drop table customerdata
go
返回：
web_customerid
-------------------
213612
212628
218579
218776
212631
212633

（所影响的行数为 6 行）

发表于：2007-06-14 16:49:3516楼得分:0

select replace（（cast（cast（reverse（cast（reverse（c) as bigint)) as bigint) as varchar（6))
+ space（6 - len（cast（cast（reverse（cast（reverse（c) as bigint)) as bigint) as varchar（6))))), ' ', '0 ')
from t1

这样就不会把末尾应保留的0给删除了

发表于：2007-06-14 17:15:3517楼得分:0

哈哈终于解决了，我查出数据库中web_customerid居然有例如（0000523.)的数据,所以转换成bigint型时就报varchar类型不能转换为bigint

最后的sql语句是：

--1:replace（b.web_customerid, '. ', ' ') 排除所有以.结尾的数字，如不排除则报varchar不能转换为bigint类型
--2:convert（bigint, replace（b.web_customerid, '. ', ' '))将所有varchar型数据转换为bigint,自动消除前面的0
--3:left（convert（bigint, replace（b.web_customerid, '. ', ' ')), 6)通过left函数取左边6位字符
--4:最后再将此数据转换为int类型进行对比
--5:isnumeric（web_customerid)将所有不是数字类型的排除
update customerdata set
refid_temp=srd_customerid
from srd_customerdata b where
customerdata.customerid=convert（int,left（convert（bigint, replace（b.web_customerid, '. ', ' ')), 6))
and isnumeric（web_customerid)=1
and isnumeric（srd_customerid)=1

发表于：2007-06-14 17:21:1018楼得分:0

感谢大家的参与，谢谢！

下一篇：怎么样把替换后的字符合并成新的字符串?
上一篇：已经远程登录到服务器上，怎样用sql语句来分离如boy数据库

其他资讯