SQL×Ö·û´®²ð·Ö - MS-SQL Server / ÒÉÄÑÎÊÌâ

Àý£º

ÁÐ1
--------------
B978_722
A9786_721
C80516_6


ÎÒÏëÈ¡_ºóÃæµÄ×Ö·û£¬½«×ª³ÉintÐÍ

½á¹ûÈçÏ£º

ÁÐ1
--------------
722
721
6

ÇëÎÊÕâÌõÓï¾äÈçºÎд£¬Ð»Ð»¡£

---

ÒýÓÃ

select cast(right(ÁÐ1,charindex('-',ÁÐ1)-1) as int) from tb

Õâ¸öÓï¾äµ±ÁÐÖеÄ×Ö·ûΪAsss_050ʱ£¬»á³öÏÖת»»´íÎó£¬Ð»Ð»ÁË¡£

---

ÒýÓÃ

ÒýÓà 2 Â¥ ldslove µÄ»Ø¸´:
select cast(right(ÁÐ1,charindex('-',ÁÐ1)-1) as int) from tb


Õâ¸öÓï¾äµ±ÁÐÖеÄ×Ö·ûΪAsss_050ʱ£¬»á³öÏÖת»»´íÎó£¬Ð»Ð»ÁË¡£

SQL code:
if not object_id('tb') is null
drop table tb
Go
Create table tb([ÁÐ1] nvarchar(9))
Insert tb
select N'B978_722' union all
select N'A9786_721' union all
select N'C80516_6' union all
select N'Asss_050'
Go
select ÁÐ1=cast(right(ÁÐ1,len(ÁÐ1)-charindex('_',ÁÐ1))as int)
from tb
/*
ÁÐ1
-----------
722
721
6
50

(4 個資ÁÏÁÐÊܵ½Ó°響)
*/

---

SQL code:


select
cast(right(col1,charindex('_',reverse(col1))-1) as int)
from
(values('B978_722'),
('A9786_721'),
('C80516_6'),
('Asss_050')
)
tb(col1)


-----------
722