求SQL语句

Question

字段值addidata1如下：075488000900450467-075488000900450467，在SQL语句中如何判断“-”前后的差值，我是想根据=0单张卡，>0是卡段

Answer 1

笨方法
等高人 解答Declare @str varchar(50) 
set @str= '075488000900450467-075488000900450465'select CHARINDEX('-',@str,1)
select LEN(@str)--select SUBSTRING(@str,(CHARINDEX('-',@str,1)+1),(LEN(@str) - CHARINDEX('-',@str,1)))
select 'before-'=SUBSTRING(@str,0,(CHARINDEX('-',@str,1)))
,'after-'=SUBSTRING(@str,(CHARINDEX('-',@str,1)+1),(LEN(@str) - CHARINDEX('-',@str,1)))
,result = cast(SUBSTRING(@str,0,(CHARINDEX('-',@str,1))) as decimal) - CAST(SUBSTRING(@str,(CHARINDEX('-',@str,1)+1),(LEN(@str) - CHARINDEX('-',@str,1))) as decimal)

Answer 2

可以用尝试用“charindex”将该值以“-”为分隔线分成两个值，再计算就行了。

Answer 3

declare @s varchar(1000)
set @s='075488000900450467-075488000900450467'select cast(PARSENAME(REPLACE(@s,'-','.'),1) as bigint)-cast(PARSENAME(REPLACE(@s,'-','.'),2) as bigint)

Answer 4

addidata字段有可能为空，卡号长度有可能18位，也有可能19位

Answer 5

只要不超过38位，都可以用declare @s varchar(1000)
set @s='075488000900450467-075489000900450467'select cast(PARSENAME(REPLACE(@s,'-','.'),1) as decimal(38,0))-cast(PARSENAME(REPLACE(@s,'-','.'),2) as decimal(38,0))

Answer 6

create table zfy(addidata1 varchar(50))insert into zfy(addidata1)
 select '075488000900450467-075488000900450467'
select t.addidata1,t.差值,
       case when t.差值=0 then '单张卡'
            when t.差值>0 then '卡段' end '类别'
from
(select addidata1,
        cast(substring(addidata1,1,charindex('-',addidata1,1)-1) as bigint)
        -cast(substring(addidata1,charindex('-',addidata1,1)+1,50) as bigint) '差值'
 from zfy) t/*
addidata1                                          差值                   类别
-------------------------------------------------- -------------------- ------
075488000900450467-075488000900450467              0                    单张卡(1 row(s) affected)
*/

Answer 7

PARSENAME('字符串',n)返回从右往左数第n的位置的字符串

Answer 8

declare @val varchar(1000)
set @val='075488000900450467-075488000900450467'
select case when left(@val,charindex('-',@val)-1)=right(@val,len(@val)-charindex('-',@val))
then '單張卡' else '多張卡' end 

Answer 9

select cast(parsename(replace('075488000900450467-075488000900450467','-','.'),2) as bigint)-cast(parsename(replace('075488000900450467-075488000900450467','-','.'),2) as bigint)