表A数据如下:
col1
11223434
144554354
18890022
12323343
43534666
....
预期结果:
11223434
144554354
18890022如何将1122,4455,0022 ...这样的数据查出来!
col1
11223434
144554354
18890022
12323343
43534666
....
预期结果:
11223434
144554354
18890022如何将1122,4455,0022 ...这样的数据查出来!
解决方案 »
- 请问,数据库排序
- window 2003 怎么无法安装sql server 2000
- JSP中mysQL数据库读取数据问题
- 多列模糊查询 性能如何提升
- ★★100分★★System.IO.FileNotFoundException: 未能加载文件或程序集“System.Data
- SELECT COUNT(*) 计划数, 生产完成数, 完成百分比 from 数据表 group by 项目编号
- #############请教一个查询问题?#############
- Sql Server最高版本是多少?
- 数据库无法设置成单用户模式进行修复,请大家救命!!!
- 链接数据库问题!是那里有问题,请帮我!
- 关于批量更新内容的语句
- 一个建表函数 麻烦大哥们进来看看
insert into # values('11223434')
insert into # values('144554354')
insert into # values('18890022')
insert into # values('12323343')
insert into # values('43534666')
go
declare @a table(id int,val int,col1 varchar(20))
declare @cnt int
set @cnt=0
while(@cnt<10)
begin
insert into @a select @cnt,charindex(replicate(cast(@cnt as varchar(5)),2),col1),col1 from #
set @cnt=@cnt+1
end select distinct col1 from @a t where val!=0 and exists(select * from @a where abs(t.val-val)=2 and val!=0 and t.col1=col1) group by id,col1,val--结果11223434
144554354
18890022
where charindex('1122'col1)>0
or
charindex('4455'col1)>0
or
...........
CREATE TABLE dbo.#test(co varchar(20))
INSERT INTO #Test
SELECT '11223434'
UNION ALL
SELECT '144554354'
UNION ALL
SELECT '18890022'
UNION ALL
SELECT '12323343'
UNION ALL
SELECT '43534666'--得到结果
SELECT *
FROM #Test,
(SELECT str(x,1)+str(x,1)+str(y,1)+str(y,1) AS test
FROM
(SELECT DISTINCT TOP 10 colid AS x
FROM syscolumns
ORDER BY colid) a,
(SELECT DISTINCT TOP 10 colid AS y
FROM syscolumns
ORDER BY colid) b
WHERE a.x<>b.y) a
WHERE co LIKE '%'+a.test+'%' co test
-------------------- ----
18890022 0022
11223434 1122
144554354 4455(3 row(s) affected)
Create Function GetResult(@str varchar(8000))
Returns int
As
Begin
Declare @iCount int,@i int,@tmp int
Declare @Old varchar(5),@New varchar(5)
Declare @Result int
Select @iCount=len(@str)
Select @i=2,@Result=0,@tmp=0
Select @Old=substring(@str,1,1) While @i<=@iCount
Begin
Select @New=substring(@str,@i,1) if @Old=@New
Begin
Select @tmp=@tmp+1
If @tmp=1
Select @Result=@Result+1
else
Select @tmp=0
End
else
Select @tmp=0 Select @Old=@New Select @i=@i+1
End Return @ResultEndGO
--2、建立测试数据
create table #(col1 varchar(20))
insert into # values('adffeff')
insert into # values('144554354')
insert into # values('呵呵哈哈')
insert into # values('1有阿杜的ww得到')
insert into # values('43da6')
go--3、取结果
Select * From # Where dbo.GetResult(col1)>=2/*
col1
--------------------
adffeff
144554354
呵呵哈哈
*/