Hi jerryhien,
Refer the below test query for your Referance
DECLARE @Search VARCHAR(50)
SET @Search='Lap.room-01'
CREATE TABLE #Device(ID INT,DeviceNo NVARCHAR(50))
INSERT INTO #Device VALUES(1,'Lap.room-01')
INSERT INTO #Device VALUES(2,'Lap-room-02')
INSERT INTO #Device VALUES(3,'Lap_room-03')
INSERT INTO #Device VALUES(4,'Lap_Sell-01')
INSERT INTO #Device VALUES(5,'Lap.Sell-02')
INSERT INTO #Device VALUES(6,'Printer.Public-01')
INSERT INTO #Device VALUES(7,'Printer-Public-02')
SELECT * FROM #Device
WHERE REPLACE(REPLACE(REPLACE(DeviceNo,'.',''),'_',''),'-','') LIKE '%'+REPLACE(REPLACE(REPLACE(@Search,'.',''),'_',''),'-','') + '%'
DROP TABLE #Device
OUTPUT
ID |
DeviceNo |
1 |
Lap.room-01 |