CREATE FUNCTION dbo.fn_1(@str varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SET @s=SUBSTRING(@str,8,len(@str)-7)
IF CHARINDEX('/',@s)>0
SET @s=LEFT(@s,CHARINDEX('/',@s)-1)
RETURN @s
END
GO
--调用:
select * from t where dbo.fn_1(col) like '%csdn%'
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SET @s=SUBSTRING(@str,8,len(@str)-7)
IF CHARINDEX('/',@s)>0
SET @s=LEFT(@s,CHARINDEX('/',@s)-1)
RETURN @s
END
GO
--调用:
select * from t where dbo.fn_1(col) like '%csdn%'
where url like ‘%???%'
declare @t table(re varchar(100))
insert @t select 'http://www.csdn.net'
union all select 'http://www.csdn.com/index.aspx'
union all select 'http://www.sina.com'
union all select 'http://www.sohu.con'
union all select 'http://zhangzs8896.com'--搜索
declare @c varchar(10)
set @c='csdn'select * from @t
where @c=substring(re,charindex(@c,re),+len(@c))--结果:
re
---------------------
http://www.csdn.net
http://www.csdn.com/index.aspx
http://www.csdn.net
http://www.csdn.com/index.aspx
http://www.sina.com/index.aspx
http://www.sohu.con
http://www.sohu.con/index.aspx统计出来的就是 sohu 和 csdn 而且我是要统计某一个位置相同的,而不是用count来统计
declare @t table(col varchar(100))
insert @t select 'http://www.csdn.net'
union all select 'http://www.csdn.com/index.aspx'
union all select 'http://www.sina.com'
union all select 'http://www.sina.com.cn'
union all select 'http://www.sohu.con'
union all select 'http://www.yahoo.com'select substring( col, charindex('.',col)+1, charindex('.',col,charindex('.',col)+1)-charindex('.',col) -1) from @t group by substring( col, charindex('.',col)+1, charindex('.',col,charindex('.',col)+1)-charindex('.',col) -1)--结果:
/*
csdn
sina
sohu
yahoo
*/
where substring(字段,11,3)='???'
select * from 表
where substring(字段,12,3)='???'
select substring(字段,12,3) from 表
group by substring(字段,12,3)
having count(1)>1