http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html?seed=1165773024 --提取英文 IF OBJECT_ID('DBO.GET_STR') IS NOT NULL DROP FUNCTION DBO.GET_STR GO CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-z]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'') END RETURN @S END GO --测试 PRINT DBO.GET_STR('呵呵ABC123ABC') GO
--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
SET @A='1234ASDF34ASDF'WHILE PATINDEX('%[^a-z]%',@A) > 0
SELECT @A=stuff(@A,patindex('%[^a-z]%',@A),1,'')SELECT @A
有一字段为:'product_id'
这个字段里每条记录都有数字跟英文
我要查询出这个字段里的英文出来!.
SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(字段,'0','')
,1,'')
,2,'')
,3,'')
,4,'')
,5,'')
,6,'')
,7,'')
,8,'')
,9,'')
FROM tb
--查询
select dbo.GET_STR(字段名) from 表名