Các bài báo kỹ thuật, nội dung và tài nguyên dành cho các Chuyên gia CNTT làm việc trong lĩnh vực công nghệ của Microsoft

Bạn đang xem: sql tìm kiếm một phần của chuỗi

Giới thiệu

Trong bài đăng này, chúng ta hãy xem cách tìm kiếm một chuỗi / cụm từ trong cơ sở dữ liệu SQL Server bằng giải pháp kết hợp của toán tử T-SQL LIKE & amp; Hàm Grep R. Hiện tại, các tùy chọn tồn tại trong SQL Server để thực hiện thao tác tìm kiếm là

  1. Toán tử LIKE
  2. Sử dụng các hàm chuỗi CHARINDEX, PATINDEX
  3. Tìm kiếm toàn văn bản

Hãy xem xét ví dụ dưới đây: Để chỉ tìm kiếm và trả lại các bản ghi có chuỗi “VAT”. Kết quả mong đợi là trả về bản ghi 1,5 & amp; 6.

DECLARE
@Tmp BẢNG
(Id INT , Descrip
VARCHAR (500))

INSERT
@Tmp CHỌN
1, 'cách tính VAT của tôi không chính xác'

INSERT
@Tmp CHỌN
2, 'Số riêng'

INSERT
@Tmp CHỌN
3, 'Innnovation model'

INSERT
@Tmp CHỌN
4, 'ELEVATE'

INSERT
@Tmp CHỌN
5, 'tổng VAT'

INSERT
@Tmp CHỌN
6, 'VAT'

CHỌN
* FROM
@Tmp WHERE
Mô tả THÍCH
'VAT'

CHỌN
* FROM
@Tmp WHERE
Mô tả THÍCH
'% VAT'

CHỌN
* FROM
@Tmp WHERE
Mô tả THÍCH
'% VAT%'

CHỌN
* FROM
@Tmp WHERE
Mô tả THÍCH
'% VAT%'

CHỌN
* FROM
@Tmp WHERE
Mô tả THÍCH
'% VAT'

Như được minh họa trong ví dụ trên, để thực hiện tìm kiếm chính xác trên chuỗi, không có tùy chọn chuyển tiếp nào sử dụng hai tùy chọn đầu tiên được đề cập ở trên. Tuy nhiên, mặc dù có thể với tùy chọn thứ ba bằng cách sử dụng vị ngữ CONTAINS Full text. Danh mục văn bản đầy đủ, chỉ mục duy nhất
& amp; chỉ mục toàn văn phải được tạo trên bảng mà thao tác tìm kiếm cần được thực hiện.

Nếu tìm kiếm chính xác chuỗi cần được thực hiện trên toàn bộ cơ sở dữ liệu thì việc tạo danh mục văn bản đầy đủ, chỉ mục duy nhất & amp; chỉ mục toàn văn trên mỗi và mọi bảng sẽ không phải là một lựa chọn khả thi.

Với cách tiếp cận kết hợp [ T-SQL LIKE toán tử & amp; R grep function], hãy cho chúng tôi xem các loại tìm kiếm khác nhau có thể được thực hiện
[ Tìm kiếm theo mẫu, Tìm kiếm chính xác, Tìm kiếm nhiều mẫu và các kịch bản tìm kiếm khác – dựa trên đối chiếu, tìm kiếm phân biệt chữ hoa chữ thường / không nhạy cảm và tìm kiếm theo ký tự đại diện phức tạp].

Chúng tôi đã sử dụng
Phiên bản đánh giá SQL Server 2019
trên Windows 10 64 bit và
Cơ sở dữ liệu mẫu WideWorldImporters
SQL Server cho ví dụ này. Trong ví dụ này, chúng tôi đã sử dụng các dịch vụ R được cài đặt như một phần của SQL Server.

Cài đặt dịch vụ R
và sau đó từ SSMS bật tính năng tập lệnh bên ngoài. Khởi động lại công cụ cơ sở dữ liệu và sau đó xác minh cài đặt như đã đề cập trong MSDN.

Tập lệnh dưới đây / cách tiếp cận này sẽ hoạt động bắt đầu từ SQL Server 2016 trở lên (vì việc thực thi ngôn ngữ R bằng T-SQL đã được giới thiệu trong SQL Server 2016). Cũng xin lưu ý, không cần cài đặt thêm gói R nào cho phương pháp này.

Giải thích về cách hoạt động của phương pháp này

Một thủ tục được lưu trữ có tên “usp_SearchString” đã được tạo. Thủ tục được lưu trữ này có khả năng thực hiện các hoạt động T-SQL LIKE bình thường cũng như có thể tìm kiếm chuỗi bằng cách sử dụng hàm R grep và điều này có thể được kiểm soát thông qua tham số đầu vào.

Kết quả của thao tác tìm kiếm sẽ được lưu trữ trong một bảng có tên “Tbl_SearchString”. Ngoài ra, kết quả đầu ra sẽ được hiển thị khi kết thúc quá trình thực thi thủ tục được lưu trữ.

Dưới đây là các thông số đầu vào khác nhau của quy trình được lưu trữ và chi tiết sử dụng của nó:

Tham số
Sự mô tả

@Chuỗi tìm kiếm
Chuỗi được tìm kiếm

@Loại tìm kiếm

ES – Tìm kiếm Chính xác bằng R

PS – Tìm kiếm mẫu sử dụng R

MPS – Tìm kiếm nhiều mẫu (điều kiện HOẶC) sử dụng R

NTLS – T-SQL Giống như Tìm kiếm Thông thường

@Cuộc thi đấu
0 = THÍCH Tìm kiếm,
1 = KHÔNG THÍCH Tìm kiếm

@IgnoreCase

1 = tìm kiếm không phân biệt chữ hoa chữ thường,

0 = Tìm kiếm phân biệt chữ hoa chữ thường

(Nếu @IgnoreCase LÀ KHÔNG ĐỦ thì mặc định: tìm kiếm không phân biệt chữ hoa chữ thường)

@SearchSQLMetadata
Tìm kiếm định nghĩa sql cho sự hiện diện của chuỗi đầu vào.
1 = Tìm kiếm,
0 = Không tìm kiếm

@SchemaName

Danh sách các đối tượng được tìm kiếm nằm trong giản đồ (Có thể chuyển nhiều lược đồ, phân tách bằng Dấu phẩy)

@ObjectlisttoSearch
Danh sách các đối tượng được tìm kiếm (Có thể chuyển nhiều bảng, phân tách bằng Dấu phẩy)

@SearchCollate
Đối với @SearchType = NTLS nếu @IgnoreCase = 0.
Để tìm kiếm dựa trên đối chiếu cụ thể, mặc định – COLLATE Latin1_General_CS_AS

Nếu cả @ObjectlisttoSearch & amp; @SchemaName trống sau đó toàn bộ cơ sở dữ liệu được tìm kiếm bao gồm các định nghĩa đối tượng SQL

Xin lưu ý:

@SearchType = ES, PS, MPS sử dụng tập lệnh R để tìm kiếm chuỗi

@SearchType = NTLS thực hiện tìm kiếm T-SQL LIKE bình thường

Tìm kiếm nhiều chuỗi phải luôn được phân cách bằng dấu sổ.

@ObjectlisttoSearch, @SchemaName phải luôn được phân tách bằng dấu phẩy nếu nhiều giá trị được chỉ định.

Tập lệnh

SỬ DỤNG
[ WideWorldImporters]

ĐI

--Lưu ý: Trước khi biên dịch SP này, hãy tìm kiếm sqlConnString và cung cấp Tên cơ sở dữ liệu, tên người dùng & amp; mật khẩu cho kết nối SQL R

TẠO HOẶC THAY THẾ PROC usp_SearchString (@SearchString NVARCHAR (MAX),

@SearchType VARCHAR ( 4 ),

@Match BIT,

@IgnoreCase BIT,

@SearchSQLMetadata CHAR ( 1 ),

@SchemaName NVARCHAR ( 50 ),

@ObjectlisttoSearch NVARCHAR (MAX),

@SearchCollate NVARCHAR ( 500 )

)

/ **************************************** *********************************

=================

THÔNG SỐ ĐẦU VÀO:

=================

@SearchString - Chuỗi được tìm kiếm

@SearchType - ES - Tìm kiếm Chính xác bằng R

PS - Tìm kiếm mẫu sử dụng R

MPS - Multi Pattern Search - OR điều kiện sử dụng R

NTLS - T-SQL Like Search thông thường

@Match - 0 = THÍCH Tìm kiếm, 1 = KHÔNG THÍCH Tìm kiếm

@IgnoreCase - 1 = tìm kiếm không phân biệt chữ hoa chữ thường, 0 = Tìm kiếm phân biệt chữ hoa chữ thường (Nếu @IgnoreCase LÀ NULL thì mặc định: tìm kiếm không phân biệt chữ hoa chữ thường)

@SearchSQLMetadata - Tìm kiếm định nghĩa sql về sự hiện diện của chuỗi đầu vào. 1 = Tìm kiếm, 0 = Không tìm kiếm

@SchemaName - Danh sách các đối tượng được tìm kiếm nằm trong giản đồ (Có thể chuyển nhiều lược đồ, phân tách bằng Dấu phẩy)

@ObjectlisttoSearch - Danh sách các đối tượng được tìm kiếm (Có thể chuyển nhiều bảng, phân tách bằng Dấu phẩy)

--IF CẢ @ObjectlisttoSearch & amp; @SchemaName ĐƯỢC BLANK KHI TÌM KIẾM TOÀN BỘ CƠ SỞ DỮ LIỆU BAO GỒM CÁC ĐỊNH NGHĨA SQL

@SearchCollate - Đối với @SearchType = NTLS nếu @IgnoreCase = 0. Để tìm kiếm dựa trên đối chiếu cụ thể, mặc định - COLLATE Latin1_General_CS_AS

***************************************** ************************************ /

Xem Thêm  nền-vị trí | Thủ thuật CSS - vị trí ảnh nền css

AS

BEGIN

BẬT TÀI KHOẢN;

IF @SearchType IN ( 'ES' , 'PS' , 'MPS' , 'NTLS' )

BEGIN

DECLARE @ExecutedBy NVARCHAR ( 200 ) = CURRENT_USER

DECLARE @Serv NVARCHAR ( 200 ) = CONCAT (CHAR ( < mã> 39 ), CHAR ( 39 ), @@ SERVERNAME, CHAR ( 39 < mã>), CHAR ( 39 ))

IF ISNULL (@SchemaName, '' ) & lt; & gt;
'' HOẶC ISNULL (@ObjectlisttoSearch, '' ) & lt; & gt;
''

BEGIN

/ **** Danh sách các cột trong bảng được tìm kiếm **** /

DECLARE @TableColList TABLE (Cols NVARCHAR (MAX), colname NVARCHAR ( 200 ), Tbl NVARCHAR ( 128 ), TblCol
NVARCHAR (
100 ), ColType NVARCHAR ( 150 ))

INSERT @TableColList

CHỌN

TRƯỜNG HỢP KHI TY.name IN ( 'date' , 'datetime2' , 'datetimeoffset' , 'time' , 'timestamp' )

THEN CONCAT ( 'TRY_CONVERT (' , 'VARCHAR (MAX), ' , C.name, ')
AS '
, QUOTENAME (C.NAME))

ELSE C.name END Cột - Để bao gồm các chuyển đổi kiểu dữ liệu kém khi được chuyển đến khung dữ liệu R

, C.name

, CONCAT (SCHEMA_NAME (T.SCHEMA_ID), '.'. , T.name ) Tên bảng

, CONCAT (SCHEMA_NAME (T.SCHEMA_ID), '.'. , T.name , '.' , C.name)
TblCol

, TY.name

TỪ Sys.tables T

THAM GIA sys.columns C

BẬT T.object_id = C.object_id

THAM GIA sys.types TY

ON C. [ user_type_id] = TY. [ user_type_id]

- Bỏ qua các kiểu dữ liệu không bắt buộc

Ở ĐÂU TY.name KHÔNG CÓ TRONG ( 'address' , 'varbinary' , 'binary' , 'text' ,
'ntext' ,
'image' , 'hierarchyid' ,
'xml' ,
'sql_variant' )

AND (Schema_name (T.schema_id) IN (CHỌN giá trị TỪ STRING_SPLIT (@SchemaName,
',' ))

HOẶC CONCAT (SCHEMA_NAME (T.SCHEMA_ID), '.' , T.name) IN (CHỌN giá trị TỪ STRING_SPLIT (@ObjectlisttoSearch ,
',' )))

HẾT ELSE

BEGIN

INSERT @TableColList

CHỌN

TRƯỜNG HỢP KHI TY.name IN ( 'date' , 'datetime2' , 'datetimeoffset' , 'time' , 'timestamp' )

THEN CONCAT ( 'TRY_CONVERT (' , 'VARCHAR (MAX), ' , C.name, ')
AS '
, QUOTENAME (C.NAME))

ELSE C.name END Cột - Để bao gồm các chuyển đổi kiểu dữ liệu kém khi được chuyển đến khung dữ liệu R

, C.name

, CONCAT (SCHEMA_NAME (T.SCHEMA_ID), '.'. , T.name ) Tên bảng

, CONCAT (SCHEMA_NAME (T.SCHEMA_ID), '.'. , T.name , '.' , C.name)
TblCol

, TY.name

TỪ Sys.tables T

THAM GIA sys.columns C

BẬT T.object_id = C.object_id

THAM GIA sys.types TY

ON C. [ user_type_id] = TY. [ user_type_id]

- Bỏ qua các kiểu dữ liệu không bắt buộc

Ở ĐÂU TY.name KHÔNG CÓ TRONG ( 'address' , 'varbinary' , 'binary' , 'text' ,
'ntext' ,
'image' , 'hierarchyid' ,
'xml' ,
'sql_variant' )

HẾT

DROP TABLE NẾU TỒN TẠI #ExportTablesList

TẠO BẢNG #ExportTablesList (Rn NHẬN DẠNG LỚN ( 1 , 1 ) , cols
NVARCHAR (
500 ), colname NVARCHAR ( 200 ), tbl NVARCHAR ( 200 ), ColType
NVARCHAR (
200 ))

IF @SearchSQLMetadata =
1 OR (@SearchSQLMetadata & lt; & gt;
0 AND (ISNULL (@SchemaName, '' ) =
'' AND ISNULL (@ObjectlisttoSearch, '' ) =
'' ))

BEGIN

INSERT #ExportTablesList (cols, tbl, ColType) SELECT
'CONCAT (' '& lt;' ', object_schema_name (sm.object_id),' '.' < /code>',object_name(sm.object_id),''|'',o.type_desc
COLLATE Latin1_General_100_CI_AS, '
' & gt; ' ', sm.definition) Định nghĩa AS '

, 'sys.sql_modules AS sm Tham gia sys.objects AS o ON sm.object_id = o.object_id'

, 'sql_modules'

HẾT

- Sao chép danh sách đối tượng

; CÓ khử trùng

AS

(

SELECT *, ROW_NUMBER () HẾT (PHẦN THEO Tbl, Cols ĐẶT HÀNG THEO Cols) Rn FROM @TableColList

)

CHÈN VÀO #ExportTablesList

CHỌN cols, colname, tbl, ColType FROM debup

WHERE Rn =
1

VÀ tbl & lt; & gt;
'dbo.Tbl_SearchString'

/ **** Danh sách các cột trong bảng được tìm kiếm **** /

IF (SELECT COUNT ( 1 ) FROM #ExportTablesList) & lt; & gt;
0

BEGIN

--Bảng để giữ kết quả tìm kiếm

NẾU KHÔNG TỒN TẠI (CHỌN
1 FROM sys.tables WHERE name =
'Tbl_SearchString' )

BEGIN

TẠO BẢNG
[ dbo]. [ Tbl_SearchString] (

[ RunId] FLOAT,

[ SearchIndex] BIGINT,

[ SearchValue] NVARCHAR (MAX),

[ NoOfOccurance] FLOAT,

[ ObjectName] NVARCHAR ( 200 ),

[ ColumnNameORDefinition] NVARCHAR ( 200 ),

[ SqlDatatype] NVARCHAR ( 200 ),

[ InputParameter] NVARCHAR ( 800 ),

[ ExecutedBy] NVARCHAR ( 200 ),

[ ExecutedAt] DATETIME

)

HẾT

DECLARE @RunId FLOAT

SELECT @RunId = COALESCE (MAX ( [ RunId]), 0 ) + 1
FROM [ dbo]. [ Tbl_SearchString]

- Xử lý để lưu trữ các tham số đầu vào

DECLARE @Input NVARCHAR (MAX) = CONCAT (

'@SearchString & gt; '
, TRƯỜNG HỢP KHI @SearchString = '' HOẶC @SearchString LÀ KHÔNG ĐẦY ĐỦ 'NULL'
ELSE @SearchString END

, ', @ SearchType & gt; '
, TRƯỜNG HỢP KHI @SearchType = ''
HOẶC @SearchType LÀ KHÔNG ĐẦY ĐỦ 'NULL'
ELSE @SearchType END

, ', @ Match & gt; '
, COALESCE (@Match, 0 )

, ', @ ignoreCase & gt; '
, COALESCE (@IgnoreCase, 1 )

, ', @ SearchSQLMetadata & gt; '
, TRƯỜNG HỢP KHI @SearchSQLMetadata = ''
HOẶC @SearchSQLMetadata LÀ KHÔNG ĐẦY ĐỦ 'NULL'
ELSE @SearchSQLMetadata END

, ', @ SchemaName & gt; '
, TRƯỜNG HỢP KHI @SchemaName = ''
HOẶC @SchemaName LÀ KHÔNG ĐẦY ĐỦ 'NULL'
ELSE @SchemaName END

, ', @ ObjectlisttoSearch & gt; '
, TRƯỜNG HỢP KHI @ObjectlisttoSearch = ''
HOẶC @ObjectlisttoSearch LÀ KHÔNG ĐẦY ĐỦ 'NULL'
ELSE @ObjectlisttoSearch END)

--Bằng
default tìm kiếm không phân biệt chữ hoa chữ thường

SELECT @IgnoreCase = COALESCE (@IgnoreCase, 1 )

–Bằng
default LIKE tìm kiếm

SELECT @Match = COALESCE (@Match, 0 )

IF @SearchType =
'NTLS'

BEGIN

DECLARE @SearchStrings TABLE (Id INT IDENTITY ( 1 , 1 ) ,Sợi dây
NVARCHAR (MAX))

INSERT @SearchStrings

CHỌN giá trị TỪ STRING_SPLIT (@SearchString,
'|' )

CẬP NHẬT #ExportTablesList SET Tbl =
'sys.sql_modules' , colname =
'định nghĩa'

WHERE ColType =
'sql_modules'

SET @SearchCollate = TRƯỜNG HỢP KHI @SearchCollate =
'' THEN NULL ELSE @SearchCollate END

DECLARE @COLLATE NVARCHAR ( 100 )

SET @COLLATE = TRƯỜNG HỢP KHI @IgnoreCase =
0 THÌ TRƯỜNG HỢP KHI @SearchCollate =
'' HOẶC @SearchCollate LÀ KHÔNG CÓ THÌ
'COLLATE Latin1_General_CS_AS'

ELSE CONCAT ( 'COLLATE' , @ SearchCollate, '
'
) HẾT

ELSE CHAR ( 32 ) HẾT

DECLARE @SearchOperator NVARCHAR ( 100 )

SET @SearchOperator = CASE WHEN @Match =
1 THEN
'KHÔNG THÍCH' ELSE
'LIKE' HẾT

DECLARE @WHEREClause NVARCHAR (MAX)

; VỚI CTE

AS

(

CHỌN
'SearchValue' + @SearchOperator + '' '' + String + '' '' + @ COLLATE
WhereClause FROM @SearchStrings

)

SELECT @WHEREClause = STUFF (

(CHỌN
'OR' + WhereClause FROM

(CHỌN WhereClause TỪ CTE) NHƯ T CHO XML PATH ( '' )), 2 , 2 , '' )

HẾT

SET @SearchString = TRƯỜNG HỢP KHI @SearchType =
'ES' THÌ THAY THẾ (@SearchString, '"' , '' ) ELSE @SearchString
HẾT

/ **** Lặp qua danh sách Đối tượng ở trên và thực thi tập lệnh R **** /

DECLARE @I INT =
1

, @ SQL NVARCHAR (MAX) = N ''

< / p>

, @ RScript NVARCHAR (MAX) = N ''

< / p>

, @ tblname NVARCHAR ( 128 )

, @ Colname NVARCHAR ( 200 )

, @ Sqltype NVARCHAR ( 100 )

WHILE @I & lt; = (CHỌN TỐI ĐA (Rn) TỪ #ExportTablesList)

BEGIN

SELECT @SQL = CONCAT ( 'SELECT' , Cols, < mã> '
FROM '
, tbl)

, @ tblname = Tbl

, @ Colname = CASE KHI @SearchType IN ( 'ES' , 'PS' )
THEN cols ELSE colname END

, @ Sqltype = ColType

FROM #ExportTablesList WHERE Rn = @I

IF @SearchType IN ( 'ES' , 'PS' , 'MPS' )

BEGIN

SET @RScript = '

# Cung cấp chi tiết thông tin xác thực DB để lưu trữ kết quả đầu ra trong bảng

sqlConnString & lt; -
"Driver = SQL Server; Server = serv; Database = WideWorldImporters; Uid = sa; Pwd = password"

# chức năng đếm không có lần xuất hiện nào

countCharOccurrences & lt; - function (char, string, Type) {

if (Nhập == "ES" )

{

Boundchar & lt; - paste 0 ( "\\ b " , char, " \\ b " , sep
=
"" )

string 1
& lt; - gsub (Boundchar, "" , string, ignore.case = ignoreCase)

}

string 1
& lt; - gsub (char, "" , string, ignore.case = Bỏ quaCase)

return ((nchar (string) - nchar (string 1 )) / nchar ( char))

}

#getting nhập tên cột tập dữ liệu vào một biến
"c"

c & lt; - colnames (InputDataSet)

if (SearchType ==
"ES" )

{

ExactString & lt; - paste 0 ( "\\ b " , SearchString, " \\ b " , sep
=
"" )

Output & lt; - as.data.frame (grep (ExactString, InputDataSet

[ [< /code>

c]],ignore.case = Bỏ qua, invert
= Match))

colnames (Output) [ 1 ] & lt; -
"SearchIndex"

Đầu ra $ SearchValue & lt; - grep (ExactString, InputDataSet

[ [ < /p>c]],ignore.case = Bỏ quaCase, giá trị = TRUE, invert
= Match)

Đầu ra $ NoOfOccurance & lt; - countCharOccurrences (Chuỗi tìm kiếm, Đầu ra $ SearchValue, Loại tìm kiếm)

}

if (SearchType ==
"PS" || SearchType ==
"MPS" )

{

Output & lt; - as.data.frame (grep (SearchString, InputDataSet

[ [< /code>

c]],ignore.case = Bỏ qua, invert
= Match))

colnames (Output) [ 1 ] & lt; -
"SearchIndex"

Đầu ra $ SearchValue & lt; - grep (SearchString, InputDataSet

[ [ < /p>c]],ignore.case = Bỏ quaCase, giá trị = TRUE, invert
= Match)

if (SearchType ==
"PS" ) {

Đầu ra $ NoOfOccurance & lt; - countCharOccurrences (SearchString, Output $ SearchValue, SearchType)}

}

Đầu ra $ ObjectName & lt; - rep (tblname, nrow (Đầu ra))

Output $ ColumnNameORDefinition & lt; - rep (c, nrow (Output))

Output $ SqlDatatype & lt; - rep (Sqltype, nrow (Output))

Đầu ra $ ObjectName [ Đầu ra $ SqlDatatype ==
"sql_modules" ] & lt; -
"sql_modules"

Output $ InputParameter & lt; - rep (Input, nrow (Output))

Đầu ra $ ExecutedBy & lt; - rep (ExecutedBy, nrow (Đầu ra))

Output $ ExecutedAt & lt; - rep ( format (Sys.time (), usetz = FALSE), nrow (Đầu ra))

Đầu ra $ RunId & lt; - rep (RunId, nrow (Đầu ra))

sqlDS & lt; - RxSqlServerData (connectionString = sqlConnString, table =
"Tbl_SearchString" )

rxDataStep (inData = Output, outFile = sqlDS, append =
"row" )

'

EXEC sp_execute_external_script

@language = N 'R'

, @ script = @RScript

, @ input_data_ 1
= @SQL

, @ params = N '@ SearchString NVARCHAR (MAX), @ SearchType VARCHAR ( 4 ), @ Match
BIT, @ ignoreCase BIT, @ Input NVARCHAR (MAX)

, @ tblname NVARCHAR ( 128 ), @ Sqltype NVARCHAR ( < mã> 150 ), @ ExecutedBy
NVARCHAR (
200 ), @ RunId FLOAT

, @ Serv NVARCHAR ( 200 ) '

< / p>

, @ SearchString = @SearchString

, @ SearchType = @SearchType

, @ Match = @Match

, @ ignoreCase = @IgnoreCase

, @ Input = @Input

, @ tblname = @tblname

, @ Sqltype = @Sqltype

, @ ExecutedBy = @ExecutedBy

, @ RunId = @RunId

, @ Serv = @Serv

HẾT

IF @SearchType =
'NTLS'

BEGIN

CHÈN
[ dbo]. [ Tbl_SearchString] ( [ RunId], [ SearchIndex], [ SearchValue], [ ObjectName]

, [ ColumnNameORDefinition], [ SqlDatatype], [ InputParameter], [ ExecutedBy], [ ExecutedAt])

EXEC ( 'SELECT' + @ RunId + ', SearchIndex, SearchValue,' < mã> '' + @ tblname + '' ',' '' + @ Colname + '' ',' '' + @ Sqltype + '' < / code> ',' '' + @ Input + '' ',' '' + @ ExecutedBy + '' ',
GETDATE ()

FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT
1 )) SearchIndex, '+ @ Colname +'
AS SearchValue FROM '+ @ tblname +

') Tmp WHERE' + @ WHEREClause)

HẾT

SET @I = @I +
1

HẾT

/ **** Lặp qua danh sách bảng trên và thực thi tập lệnh R **** /

--Hiển thị kết quả tìm kiếm cuối cùng

CHỌN * TỪ
[ dbo]. [ Tbl_SearchString] WHERE RunId = @RunId AND ExecutedBy = CURRENT_USER

HẾT

ELSE

CHỌN
'Không có đối tượng hợp lệ nào được chuyển vào InputParameter để tìm kiếm chuỗi'
AS InvalidParameter

HẾT

ELSE

SELECT 'SearchType tham số là bắt buộc ES - Tìm kiếm chính xác, PS - Tìm kiếm theo mẫu, MPS - Tìm kiếm nhiều mẫu - OR điều kiện

, NTLS - T-SQL Like Normal Search 'AS InvalidParameter

HẾT

Khối mã thực thi mẫu:

EXEC
usp_SearchString @SearchString = 'VAT'

, @ SearchType =
'ES'

, @ Match = 0
- 0 = LIKE, 1 = NOT LIKE

, @ ignoreCase = 1
- 1 = Không phân biệt chữ hoa chữ thường, 0 = Phân biệt chữ hoa chữ thường

, @ SearchSQLMetadata = 0
- 1 = Tìm kiếm, 0 = Không tìm kiếm

, @ SchemaName = ' '

, @ ObjectlisttoSearch = ' dbo.Tmp '

, @ SearchCollate = ' '

Ảnh chụp màn hình thực thi tập lệnh mẫu

1. Chuỗi tìm kiếm trong bảng từ DB

Ví dụ 1: Nếu chúng ta muốn tìm kiếm một chuỗi "Ava" trên bảng Application.People từ cơ sở dữ liệu WideWorldImporters, chúng ta có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

2. Tìm kiếm chính xác

trong bảng từ DB

Trong ví dụ 1, chúng tôi đã thực hiện tìm kiếm theo mẫu. Nếu chúng tôi muốn thực hiện tìm kiếm chính xác chuỗi "Ava" trên bảng Application.People từ cơ sở dữ liệu WideWorldImporters, chúng tôi có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

3. Tìm kiếm phân biệt chữ hoa chữ thường chính xác

Ví dụ 3: Trong ví dụ 2, chúng tôi đã thực hiện một tìm kiếm chính xác. Nếu chúng tôi muốn thực hiện tìm kiếm phân biệt chữ hoa chữ thường chính xác cho chuỗi "Ava" trên bảng Application.People từ cơ sở dữ liệu WideWorldImporters, chúng tôi có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

4. Tìm kiếm phân biệt chữ hoa chữ thường chính xác (NTLS)

Nếu chúng tôi muốn thực hiện tìm kiếm phân biệt chữ hoa chữ thường chính xác cho chuỗi "nam" trên bảng Purchasing.PurchaseOrderLines từ cơ sở dữ liệu WideWorldImporters, chúng tôi có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

5.

Tìm kiếm phân biệt chữ hoa chữ thường chính xác (Sử dụng R)

Trong ví dụ 4, nó trả về hai hàng (bản ghi chứa cả "nam" và "nữ") khi @SearchType được đặt NTLS (Tìm kiếm Giống như T-SQL Bình thường). Nhưng chúng tôi thực sự mong đợi kết quả là một hàng với bản ghi chỉ chứa "nam". Nếu chúng ta lại làm một trường hợp chính xác
tìm kiếm nhạy cảm cho một chuỗi "nam" trên bảng Purchasing.PurchaseOrderLines từ cơ sở dữ liệu WideWorldImporters, bằng cách đặt @SearchType = 'ES' được hiển thị bên dưới:

6. Tìm kiếm nhiều chuỗi được phân tách bằng dấu gạch ngang

Nếu chúng tôi muốn thực hiện tìm kiếm nhiều chuỗi được phân tách bằng dấu sổ (chuỗi tìm kiếm "Ava", "Amy") trên bảng Application.People từ cơ sở dữ liệu WideWorldImporters, chúng tôi có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

Xin lưu ý rằng trường NoOfOccurance sẽ chỉ được điền cho @SearchType = "ES" & amp; "PS" (để tìm kiếm chuỗi đơn không có ký tự đại diện).

7. Tìm kiếm nhiều chuỗi bằng cách sử dụng T-SQL LIKE tìm kiếm thông thường

Trong ví dụ 6, tìm kiếm nhiều chuỗi đã được thực hiện bằng tập lệnh R. Nếu chúng ta muốn thực hiện tìm kiếm nhiều chuỗi tương tự bằng cách sử dụng tìm kiếm T-SQL LIKE thông thường, chúng ta có thể thử bằng cách đặt các giá trị tham số như hình dưới đây:

Cũng trong ví dụ này, hãy cho chúng tôi biết cách chúng tôi có thể tìm kiếm chuỗi với cài đặt đối chiếu cụ thể.

8. Tìm kiếm mẫu cố định

Nếu chúng tôi muốn thực hiện tìm kiếm theo mẫu cố định, giả sử nếu chúng tôi biết chuỗi được tìm kiếm là một bảng chữ cái có hai chữ cái thì chúng tôi có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

9. Cụm từ tìm kiếm từ nhiều bảng được phân tách bằng dấu phẩy

Ví dụ 9: Nếu chúng ta muốn tìm kiếm một cụm từ từ nhiều bảng được phân tách bằng dấu phẩy thì chúng ta có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

10. Tìm kiếm ngày từ nhiều giản đồ được phân tách bằng dấu phẩy

Ví dụ 10: Nếu chúng ta muốn tìm kiếm một ngày từ nhiều lược đồ được phân tách bằng dấu phẩy thì chúng ta có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

11. Chuỗi tìm kiếm có tên "Mật khẩu" trong toàn bộ cơ sở dữ liệu

Ví dụ 11: Nếu chúng ta muốn tìm kiếm một chuỗi có tên là "Mật khẩu" trong toàn bộ cơ sở dữ liệu bao gồm các định nghĩa đối tượng SQL thì chúng ta có thể thử bằng cách đặt các giá trị tham số như được hiển thị bên dưới:

12. Tìm kiếm thẻ hoang dã

Ví dụ 12: Ví dụ dưới đây cho thấy cách chúng tôi có thể thực hiện tìm kiếm theo ký tự đại diện khi tìm kiếm được thực hiện bằng tập lệnh R, hãy tham khảo các giá trị tham số như được hiển thị bên dưới:

Để biết thêm về tìm kiếm thẻ đại diện R sử dụng "?", "*", "^", "$", vui lòng xem liên kết được cung cấp trong phần tham khảo.

Tbl_SearchString lưu trữ các chi tiết về tìm kiếm được thực hiện trên một chuỗi. Nếu chúng ta muốn xem toàn bộ chi tiết (tất cả các trường khác từ một bảng) của bản ghi khớp với chuỗi đã tìm kiếm thì chúng ta có thể thử như hình dưới đây:

--Để có được các bước chạy của RunId cụ thể

CHỌN
DISTINCT
RunId

, [ ObjectName]

FROM
[ WideWorldImporters]. [ dbo]. [ Tbl_SearchString]

WHERE
RunId = 12

CHỌN
A. *

, B. *

FROM
[ WideWorldImporters]. [ dbo]. [ Tbl_SearchString] A

THAM GIA
(
CHỌN
row_number () over (
order
bởi
(
CHỌN
'A'
)) Rn, * FROM
Warehouse.StockItemHoldings) B --Thay đổi tên bảng

ON
A.SearchIndex = B.Rn

AND
A.ObjectName = 'Warehouse.StockItemHoldings'
--Thay đổi tên bảng

AND
RunId = 12 - id chạy cung cấp

Tóm tắt

R & amp; Tiện ích mở rộng ngôn ngữ Python đã được giới thiệu trong SQL Server 2016 & amp; 2017 là một phần của học máy. Với

hỗ trợ của R trong cơ sở dữ liệu Azure SQL
, cách tiếp cận mới này có thể được sử dụng rộng rãi vì nó dễ dàng, linh hoạt và được hỗ trợ trong cả Tại chỗ & amp; Cơ sở dữ liệu Azure SQL.

Bài đăng này chỉ nhằm cung cấp cho bạn một cái nhìn tổng quan về cách tiếp cận mới này để tìm kiếm các chuỗi nằm trong bất kỳ góc nào của cơ sở dữ liệu SQL Server bằng cách sử dụng tập lệnh T-SQL / R. Dựa trên yêu cầu cụ thể, điều chỉnh giải pháp được đề cập ở trên (với chuỗi R mạnh mẽ khác
package / perf2rx) có thể bao gồm bất kỳ tình huống nào.

Xem thêm

Tài liệu tham khảo khác


Xem thêm những thông tin liên quan đến chủ đề tìm kiếm sql cho một phần của chuỗi

Searching for text in SQL Server: CHARINDEX and PATINDEX

  • Tác giả: SQL Server 101
  • Ngày đăng: 2021-07-21
  • Đánh giá: 4 ⭐ ( 9768 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: In this video, we will be looking at how to search for text within bigger text searches.
    My SQL Server Udemy courses are:
    70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft-sql-server
    98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
    70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
    Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
    SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
    SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
    Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpivot-ssas-tabular-dax
    ----
    My Excel for PC courses are:
    Beginner to Excel - https://rebrand.ly/microsoft-excel-specialist
    Intermediate to Expert Advanced - https://rebrand.ly/microsoft-excel-expert
    Power Table, Get and Transform and Power Pivot - https://rebrand.ly/visualizing-data-excel
    VBA macros for Excel - https://rebrand.ly/excel-vba-pc
    ----
    In this video, we will look at:
    CHARINDEX - this is the SQL Server equivalent of INSTR or FIND. SQL Server is 1-based, so the first character is character number 1, not 0. You can either start searching from the beginning, or from partway through the string.
    PATINDEX - sometimes, looking for string literals is not sufficient. You may need to use wildcards such as % and _ as well. This is when you use PATINDEX. However, you must start searching from the beginning with PATINDEX.

Làm cách nào để tìm kiếm một chuỗi trong cơ sở dữ liệu SQL Server?

  • Tác giả: qastack.vn
  • Đánh giá: 3 ⭐ ( 8871 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: [Tìm thấy giải pháp!] Thao tác này sẽ tìm kiếm mọi cột của mọi bảng trong một cơ sở dữ…

SQL Server: Hàm xử lý chuỗi

  • Tác giả: v1study.com
  • Đánh giá: 4 ⭐ ( 8865 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Dưới đây chúng ta sẽ tìm hiểu một số hàm xử lý chuỗi phổ dụng trong SQL Server. Các bạn cần lưu ý rằng các hàm này chỉ áp dụng được cho câu lệnh SELECT.

Cách sử dụng hàm CHARINDEX trong SQL Server

  • Tác giả: hoctinonline.edu.vn
  • Đánh giá: 5 ⭐ ( 2023 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Câu Lệnh Tìm Kiếm Trong Sql (Bài 5), Mệnh Đề Like Trong Sql

  • Tác giả: cokiemtruyenky.vn
  • Đánh giá: 5 ⭐ ( 4456 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Truy vấn dữ liệu trong sql là thao tác trích xuất thông tin được lưu trữ trong các table, Thông tin được truy xuất thông qua các cột và thông tin cần trích xuất có thể thuộc một hoặc nhiều bảng

Tìm kiếm gần đúng trong SQL Server

  • Tác giả: howkteam.vn
  • Đánh giá: 5 ⭐ ( 8640 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Tìm kiếm gần đúng trong SQL Server

Tìm Kiếm Gần Đúng Trong SQL (MySQL)

  • Tác giả: tuoitreit.vn
  • Đánh giá: 5 ⭐ ( 5137 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: (MySQL&PHP)
    Rắc rối sẽ diễn ra khi có quá nhiều
    thông tin và chính ta cũng không biết
    chính xác phải tìm cái gì.
    Sau đây mình sẽ giới thiệu tới cách...

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

By ads_php