比如表A,中字段为question值为:“Lb4A3136 阻抗继电器中接入第三相电压。”要的结果是把前面的编号“Lb4A3136” 去掉,但是每题的编号不一样。但是格式是这样的:
第一个和第二个是字母,第三个是数字,第四个是字母,第五到第八个也是数字。请问如果写这样一个sql语句来删除这些多余的数据??急等。
第一个和第二个是字母,第三个是数字,第四个是字母,第五到第八个也是数字。请问如果写这样一个sql语句来删除这些多余的数据??急等。
set @s='Lb4A3136阻抗继电器中接入第三相电压'select right(@s,len(@s)-PATINDEX('%[吖-座]%',@S)+1)/*----------------------------------------------------------------------------------------------------
阻抗继电器中接入第三相电压
*/
select ltrim(right(col,len(col)-8)) col
from tb/*
update tb
set col = ltrim(right(col,len(col)-8))
*/
select substring(question,9,len(question)) from a where
(substring(question,1,1) between '0' and '9' or substring(question,1,1) between 'a' and 'z') and
(substring(question,2,1) between '0' and '9' or substring(question,2,1) between 'a' and 'z') and
(substring(question,3,1) between '0' and '9' or substring(question,3,1) between 'a' and 'z') and
(substring(question,4,1) between '0' and '9' or substring(question,4,1) between 'a' and 'z') and
(substring(question,5,1) between '0' and '9' or substring(question,5,1) between 'a' and 'z') and
(substring(question,6,1) between '0' and '9' or substring(question,6,1) between 'a' and 'z') and
(substring(question,7,1) between '0' and '9' or substring(question,7,1) between 'a' and 'z') and
(substring(question,8,1) between '0' and '9' or substring(question,8,1) between 'a' and 'z')
这两边的div标记不能去掉哦。怎么写?
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
SELECT DBO.CHINA_STR(你的列名) FROM 你的表
GO
--------------------------------------------------------------------