22,209
社区成员
发帖
与我相关
我的任务
分享
create table pard(ID int,GYLX varchar(20))
insert into pard
select 6,'0G-11DX' union all
select 7,'3J-0G' union all
select 8,'3J-0G' union all
select 9,'3J-0G-4ZB'
-- GYLX列中最后一个包含数字0的
select *
from pard
where substring(reverse(GYLX),
patindex('%[0-9]%',reverse(GYLX)),
1)='0'
/*
ID GYLX
----------- --------------------
7 3J-0G
8 3J-0G
(2 row(s) affected)
*/
-- GYLX列中最后一个包含数字4的
select *
from pard
where substring(reverse(GYLX),
patindex('%[0-9]%',reverse(GYLX)),
1)='4'
/*
ID GYLX
----------- --------------------
9 3J-0G-4ZB
(1 row(s) affected)
*/
create table #temp(ID int, GYLX varchar(100))
insert into #temp
SELECT 8,'3J-0G' UNION ALL
SELECT 9,'3J-0G-4ZB'
;WITH a1 AS
(
SELECT id,REVERSE(gylx) gylx FROM #temp
)
,a2 AS
(
SELECT *,CASE WHEN CHARINDEX('-',gylx)>0 THEN LEFT(gylx,CHARINDEX('-',gylx)-1) ELSE gylx END gylx2
FROM a1
)
SELECT * FROM #temp WHERE id =
(
SELECT MAX(id)
FROM a2
WHERE CHARINDEX('0',gylx2)>0 OR CHARINDEX('7',gylx2)>0
)
create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
7 3J-0G
*/
[/quote]
还有一种情况刚才忘说了:
比如 :
8 3J-0G
9 3J-0G-4ZB
我只想要:
8 3J-0G
不要
9 3J-0G-4ZB[/quote]
这个有什么判断规则不[/quote]
没什么判断规则,这个GYLX比较乱 什么情况都可能发生
但是都是由数字和字符组成我需要判断的是 字符串中最后一个数字是0或者别的数字(这个数字是可以随便输入的)[/quote]
这样吗:
--drop table table_1
create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G' union all
select 8 ,'3J-0G' union all
select 9 ,'3J-0G-4ZB'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by charindex('0',reverse(GYLX)) ,id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
8 3J-0G
*/
create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
7 3J-0G
*/
[/quote]
还有一种情况刚才忘说了:
比如 :
8 3J-0G
9 3J-0G-4ZB
我只想要:
8 3J-0G
不要
9 3J-0G-4ZB[/quote]
这个有什么判断规则不[/quote]
没什么判断规则,这个GYLX比较乱 什么情况都可能发生
但是都是由数字和字符组成我需要判断的是 字符串中最后一个数字是0或者别的数字(这个数字是可以随便输入的)[/quote]看看我18楼的行不行create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
7 3J-0G
*/
[/quote]
还有一种情况刚才忘说了:
比如 :
8 3J-0G
9 3J-0G-4ZB
我只想要:
8 3J-0G
不要
9 3J-0G-4ZB[/quote]
这个有什么判断规则不[/quote]
没什么判断规则,这个GYLX比较乱 什么情况都可能发生
但是都是由数字和字符组成我需要判断的是 字符串中最后一个数字是0或者别的数字(这个数字是可以随便输入的)--create function [dbo].[m_getnumberV2.0]
--(
-- @mysql_one nvarchar(200)
--)
--returns varchar(200)
--begin
-- declare @mysql_two varchar(200)
-- declare @sql_one int
-- declare @sql_two int
-- select @sql_one= patindex('%[0-9.]%',@mysql_one)
-- select @sql_two=
-- patindex('%[^0-9.]%',
-- substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))
-- if @sql_two=0
-- begin
-- select @mysql_two= substring (@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)
-- end
-- else
-- begin
-- select @mysql_two=substring (@mysql_one,@sql_one,@sql_two-1)
-- end
-- return @mysql_two;
--end
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-23 11:13:23
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[table_1]
if object_id('[table_1]') is not null drop table [table_1]
go
create table [table_1]([ID] int,[GYLX] varchar(9))
insert [table_1]
select 1,'3j-4b-5x' union all
select 2,'4b-3j-7zp' union all
select 3,'12zp' union all
select 4,'0g' union all
select 5,null
--------------开始查询--------------------------
select *
from [table_1]
WHERE [dbo].[m_getnumberV2.0](REVERSE([GYLX])) IN (0,7)
----------------结果----------------------------
/*
ID GYLX
----------- ---------
2 4b-3j-7zp
4 0g
*/
create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
7 3J-0G
*/
[/quote]
还有一种情况刚才忘说了:
比如 :
8 3J-0G
9 3J-0G-4ZB
我只想要:
8 3J-0G
不要
9 3J-0G-4ZB[/quote]
这个有什么判断规则不create table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
7 3J-0G
*/
[/quote]
还有一种情况刚才忘说了:
比如 :
8 3J-0G
9 3J-0G-4ZB
我只想要:
8 3J-0G
不要
9 3J-0G-4ZBcreate table table_1(ID int, GYLX varchar(100))
insert into table_1
select 1 ,'3j-4b-5x' union all
select 2 ,'4b-3j-7zp' union all
select 3 ,'12zp' union all
select 4 ,'0g' union all
select 5 ,'' union all
select 6 ,'0G-11DX' union all
select 7 ,'3J-0G'
go
select ID,GYLX
from
(
select *,
ROW_NUMBER() over(order by id desc) rownum
from table_1
where GYLX like '%0%' --or GYLX like '%7%'
)t
where rownum = 1
/*
ID GYLX
7 3J-0G
*/
select * from table_1 where GYLX like '%0' or GYLX like '%7'
楼主试试这个吧。