各位大大好：
我有建一資料庫，有sgift_no和egift_no兩個欄位，裡面分別是SL107100001和SL107160000，我想要查詢SL107XXXXXX(XXXXXX為數字)有在sgift_no和egift_no之間，
我用以下SQL查詢，會有結果出來，該如何修改，才會是SL107100001~SL107160000之間，謝謝。
Declare
@giftno varchar(20)
--Initialize var
set @giftno = 'SL10716;;;'
SELECT sgift_no,egift_no
FROM [ABT_WEB].[dbo].[gift_data]
where (@giftno >=sgift_no) and (@giftno <=egift_no)
已邀請的邦友 {{ invite_list.length }}/5
試試..
set @giftno = 'SL107100015'
SELECT sgift_no,egift_no
FROM [ABT_WEB].[dbo].[gift_data]
where @giftno between sgift_no and egift_no
Try:
Declare
@giftno varchar(20)
--Initialize var
set @giftno = 'SL10716;;;'
SELECT sgift_no,egift_no
FROM [ABT_WEB].[dbo].[gift_data]
where ( len(@giftno) = 11 ) and
( substring(@giftno, 1, 5) = 'SL107' ) and
( substring(@giftno, 6, 6) >= substring(sgift_no, 6, 6) ) and
( substring(@giftno, 6, 6) <= substring(egift_no, 6, 6) )
大概是這種感覺...
SELECT sgift_no, egift_no
FROM( SELECT sgift_no, egift_no,
REPLACE('SL','', sgift_no) AS S,
REPLACE('SL','', egift_no) AS E
FROM [ABT_WEB].[dbo].[gift_data]
)gift_data
WHERE REPLACE('SL','', @giftno) BETWEEN S AND E
iThome鐵人賽
看影片追技術