[SQL]找出某個字串中是否含有某個碼的字

2014-07-24

5626
0
SQL

要如何找出某個字串中是否含有某個內碼的字呢?
例如，有個內碼為9c41的字，在某個字串中，要如何Search呢?

問題

要如何找出某個字串中是否含有某個內碼呢?

舉例來說，有個內碼為9c41的字，在某個字串中，要如何Search呢?

研究

Search的話，可以用 CHARINDEX 來判斷是否大於0。

那重點是如何把 '9c41' 轉成字串來 Search 呢?

9c41是用16進位來表示，但目前是字串。

如果要將 '9c41' 轉成代表 9c41 內碼的字，需要將它轉成 varbinary 後，再轉成字串。如下，


--0.9c41 是存在某Table中的內碼表中的一個值，所以用字串變數來存放
DECLARE @searchHex varchar(10)
SET @searchHex = '9c41';

--1.轉成 varbinary (CONVERT的第3個參數，因為字串中沒有0x，所以參數值給2)，
DECLARE @searchBin VARBINARY(10)
SET @searchBin = CONVERT(varbinary(10), @searchHex, 2);
SELECT @searchBin;

--2.再將 varbinary 轉成 Varchar 就是 9c41 內碼所代表的字
DECLARE @searchChar VARCHAR(4)
SET @searchChar = CAST(@searchBin AS VARCHAR)
SELECT @searchChar

整個測試，如下，


USE tempdb
GO
--DROP TABLE CODE_MAP
CREATE TABLE CODE_MAP
(
CODE VARCHAR(4)
);

INSERT INTO CODE_MAP(CODE) VALUES('9C41');
INSERT INTO CODE_MAP(CODE) VALUES('9C42');

--DROP TABLE DOCS
CREATE TABLE DOCS
(
ID INT,
DOC_DESC VARCHAR(100)
);
--DELETE FROM DOCS;
GO

INSERT INTO DOCS(ID, DOC_DESC) VALUES (1, 'ABC NO CODE IN STRING');
INSERT INTO DOCS(ID, DOC_DESC) VALUES (2, 'ONE CODE IN STRING[' + CAST(CONVERT(varbinary(10), '9C41', 2) AS VARCHAR(10)) + ']HERE!');
INSERT INTO DOCS(ID, DOC_DESC) VALUES (3, '2 CODE IN STRING[' + CAST(CONVERT(varbinary(10), '9C41', 2) AS VARCHAR(10)) + '] and [' 
		+ CAST(CONVERT(varbinary(10), '9C42', 2) AS VARCHAR(10)) + '] here!');
INSERT INTO DOCS(ID, DOC_DESC) VALUES (4, 'ABC NO CODE IN STRING');

--找出那些資料含有 記錄的內碼
SELECT B.CODE, A.*
FROM DOCS A, CODE_MAP B
WHERE CHARINDEX(CAST(CONVERT(varbinary(10), B.CODE, 2) AS VARCHAR(10)) , DOC_DESC) > 0;

如果要看某個欄位中，包含了那些CODE，可參考將多筆資料中某一欄的資料轉作一列顯示，如下，


SELECT T1.*
	,STUFF(( SELECT  ', ' + B.CODE
	FROM CODE_MAP B
	WHERE CHARINDEX(CAST(CONVERT(varbinary(10), B.CODE, 2) AS VARCHAR(10)), T1.DOC_DESC) > 0 
	FOR
	XML PATH('')
	), 1, 1, '') AS [CODES]
FROM (SELECT DISTINCT A.*
FROM DOCS A, CODE_MAP B
WHERE CHARINDEX(CAST(CONVERT(varbinary(10), B.CODE, 2) AS VARCHAR(10)) , DOC_DESC) > 0) T1;

如果要知道各在那個地方，以方便比對的話，可以建立一個Function來Replace哦! 如下，


IF OBJECT_ID (N'dbo.REPLACE_WITH_CODE', N'FN') IS NOT NULL
    DROP FUNCTION dbo.REPLACE_WITH_CODE;
GO
CREATE FUNCTION dbo.REPLACE_WITH_CODE (@DATA VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @Result VARCHAR(MAX)
	SET @Result = @DATA;
	SELECT @Result = REPLACE(@Result, CAST(CONVERT(varbinary(10), B.CODE, 2) AS VARCHAR(10))
	,  '@' + B.CODE + '@')
	FROM CODE_MAP B 
	RETURN(@Result);
END;
GO

--加入呈現REPLACE後的資料
SELECT T1.*
	,STUFF(( SELECT  ', ' + B.CODE
	FROM CODE_MAP B
	WHERE CHARINDEX(CAST(CONVERT(varbinary(10), B.CODE, 2) AS VARCHAR(10)), T1.DOC_DESC) > 0 
	FOR
	XML PATH('')
	), 1, 1, '') AS [CODES]
, dbo.REPLACE_WITH_CODE(T1.DOC_DESC) AS [REPLACE_CODES]
FROM (SELECT DISTINCT A.*
FROM DOCS A, CODE_MAP B
WHERE CHARINDEX(CAST(CONVERT(varbinary(10), B.CODE, 2) AS VARCHAR(10)) , DOC_DESC) > 0) T1;