SQL 查詢 %跟 _

2020-08-23

這麼久以來都在用%%%%%%% 現在終於有多餘的選項了有空果然多讀書......

CREATE TABLE [dbo].[test_a](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](255) NULL,
	[nvmax] [nvarchar](max) NULL,
	[nv] [nchar](10) NULL,
 CONSTRAINT [PK_test_a] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

INSERT INTO [dbo].[test_a]  VALUES ( N'梨乾',N'NVMAX', N'nv190')
INSERT INTO [dbo].[test_a]  VALUES ( N'梨子',N'NVMAX子', N'nv200')
INSERT INTO [dbo].[test_a]  VALUES ( N'梨子樹',N'NVMAX樹', N'nv210')



 SELECT  NAME   
FROM test_a   
WHERE NAME LIKE  N'梨_'

 SELECT  NAME   
FROM test_a   
WHERE NAME LIKE  N'梨__'

 SELECT  NAME   
FROM test_a   
WHERE NAME LIKE  N'梨%'

從執行計畫看效能是一樣的但是資料正確性來說起碼可以後續少下一個限定長度
"%": 把後面無限長度都抓來比對
"_": 限定1個字元 match
"__" :2個以此類推

回首頁