sql中nvarchar 取小数点前面和后面

Question

select rate from users where username='test'结果
3.6想取小数点前和小数点后  数据最多小数点后1位如何实现呢 求助

Answer 1

charindex找到小数点的位置
len得到字符串长度

Answer 2

users 表 里面的 rate列的数据 是nvarchar数据类型 查询条件 username='test'
数据是3.6 现在想取到 3 和 6 

Answer 3

                                                                                                                                                                                                                                                                 DECLARE @str VARCHAR(10)
SET @str=3.6
SELECT PARSENAME(@str, 2) , PARSENAME(@str, 1)
 
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 
3                                                                                                                                6（所影响的行数为 1 行）

Answer 4

SELECT *,rate,PARSENAME(rate, 2) 'fpart', PARSENAME(rate, 1) 'apart' 
FROM user WHERE username='test'

Answer 5

declare @str varchar(10)
set @str='3.6'--第一种
select parsename(@str,2),parsename(@str,1)
--第二种
select left(@str,charindex('.',@str)-1),right(@str,charindex('.',@str)-1)
--第三种
select substring(@str,1,charindex('.',@str)-1),
substring(@str,charindex('.',@str)+1,len(@str))

Answer 6

declare @var varchar(10)
set @var = '12345678.9'
select left(@var,len(@var) - 2), right(@var, 1)

Answer 7

select left(rate, len(rate) - 2), right(rate, 1) from users where username='test'

Answer 8

declare @str varchar(10)
set @str='3.6'--第一种
select parsename(@str,2),parsename(@str,1)
--第二种
select left(@str,charindex('.',@str)-1),right(@str,charindex('.',@str)-1)
--第三种
select substring(@str,1,charindex('.',@str)-1),
substring(@str,charindex('.',@str)+1,len(@str))

Answer 9

declare @rate decimal(8,3)
set @rate=8.3
select floor(@rate),floor(@rate*10)-floor(@rate)*10
/*
--------------------------------------- ---------------------------------------
8                                       3(1 行受影响)
*/