求一个数据转换的查询语句

[fxsy6669]

select PublicationScope from MediaInfo

结果如下

PublicationScope
1.2
600
7.5
1亿5000万
11万
000
2万/日访问量
2.5亿
null
(空值)
60000IP/天
252万/日访问量

我想要的是将有单位的转换为数字型的，如果为null或空值，就显示为0，这个查询语句怎么写呢？

[华夏小卒]

[永生天地]

[Quote=引用 1 楼 js_szy 的回复:]

[/Quote]
几乎不可完成

[月光下的土豆]

关注中~~~~~~~

[月光下的土豆]

感觉应该自己写个函数

[zzz1975]

if ** case ，建议自己写函数

[htl258_Tony]

要理出一个规则，根据规则写自定义函数。

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([PublicationScope] [nvarchar](20))
INSERT INTO [tb]
SELECT '1.2' UNION ALL
SELECT '600' UNION ALL
SELECT '7.5' UNION ALL
SELECT '1亿5000万' UNION ALL
SELECT '11万' UNION ALL
SELECT '000' UNION ALL
SELECT '2万/日访问量' UNION ALL
SELECT '2.5亿' UNION ALL
SELECT NULL UNION ALL
SELECT '(空值)' UNION ALL
SELECT '60000IP/天' UNION ALL
SELECT '252万/日访问量'

--SELECT * FROM [tb]

-->SQL查询如下:
IF OBJECT_ID('f_test')>0
	DROP FUNCTION f_test
GO
CREATE FUNCTION f_test(
	@s NVARCHAR(100)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
	IF ISNULL(@s,'')='' OR @s LIKE '%空值%' 
		RETURN 0
	SET @s=@s+'Z'
	DECLARE @r DECIMAL(18,2)
	WHILE PATINDEX('%[^0-9.]%',@s)>0
	BEGIN
		SET @r = ISNULL(@r,0)
			   + CAST(LEFT(@s,PATINDEX('%[^0-9.]%',@s)-1) AS FLOAT)
			   * CASE LEFT(STUFF(@s,1,PATINDEX('%[^0-9.]%',@s)-1,''),1)
			         WHEN '亿' THEN Power(CAST(10 AS BIGINT),8)
			         WHEN '万' THEN 10000
			         ELSE 1
			     END
	    SET @s=STUFF(@s,1,PATINDEX('%[^0-9.]%',@s),'')		   		
	END
	RETURN @r
END
GO

SELECT [PublicationScope],dbo.f_test([PublicationScope]) result FROM tb
/*
PublicationScope     result
-------------------- ---------------------------------------
1.2                  1.20
600                  600.00
7.5                  7.50
1亿5000万              150000000.00
11万                  110000.00
000                  0.00
2万/日访问量              20000.00
2.5亿                 250000000.00
NULL                 0.00
(空值)                 0.00
60000IP/天            60000.00
252万/日访问量            2520000.00

(12 行受影响)
*/

参考示例

[guguda2008]

如TONY哥所说，需要一个计量单位表

[xushilin000000000]

变通一下不好吗？


没求得法呀。

[zc_0101]

[Quote=引用 7 楼 htl258 的回复:]

SQL code
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([PublicationScope] [nvarchar](20))
INSERT INTO [tb]
SELECT '1.2' UNION ALL
SELECT '600' UNION AL……
[/Quote]
强悍。。

[mechellerong]

[Quote=引用 7 楼 htl258 的回复:]
SQL code
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([PublicationScope] [nvarchar](20))
INSERT INTO [tb]
SELECT '1.2' UNION ALL
SELECT '600' UN……
[/Quote]

很牛，学习了

[jeromeccf]

[Quote=引用 7 楼 htl258 的回复:]

SQL code
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([PublicationScope] [nvarchar](20))
INSERT INTO [tb]
SELECT '1.2' UNION ALL
SELECT '600' UNION AL……
[/Quote]
牛人啊