MYSQL - Use variable with Regex in select query to get records

mysql regexp
mysql regex tester
mysql group by regex
regular expression in sql w3schools
mysql regex replace special characters

There are strings in table

+1-123-456-7890 
11234567890
+1 1234567890
1777555999

I have variable $phoneNumber. I want to pass variable in sql query to get matching records. example: $phoneNumber holds the value 1234567890 and using same variable I want to fetch following records in one query

+1-123-456-7890 
11234567890
+1 1234567890

I want to select both records in one query. how can I match data (11234567890) with above saved numbers?


We can do this following the replacement logic already suggested here combined with regular expressions, to match only the phone numbers with the format you want to target:

WITH yourTable AS (
    SELECT '+1-123-456-7890' AS phone UNION ALL
    SELECT '+1 1234567890' UNION ALL
    SELECT '+6512345678'
)

SELECT
    phone,
    REPLACE(REPLACE(REPLACE(phone, ' ', ''), '+', ''), '-', '') AS common_phone
FROM yourTable
WHERE phone REGEXP '\\+[0-9][[:space:]-][0-9]{3}-?[0-9]{3}-?[0-9]{4}';

Demo

MySQL REGEXP: Querying Data Using Regular Expressions, For example, you can use regular expressions to search for email, IP address, phone the meaning of the regular expression in the comment of the SQL statement. Suppose you want to find all products whose last names start with character A, MySQL Copy Table · MySQL Variables · MySQL SELECT INTO Variable  The abbreviation of regular expressions is regex or regexp. MySQL REGEXP operator. MySQL adapts the regular expression implemented by Henry Spencer. MySQL allows you to match pattern right in the SQL statements by using REGEXP operator. The following illustrates the syntax of the REGEXP operator in the WHERE clause:


Try this !

Regex :

 [\+]?[0-9](?:[-| ])?\d{3}[-]?\d{3}[-]?\d{4}

Verify through regxe101:

MySQL Tutorial :: 4.4.7 Pattern Matching, Working with NULL Values Using User-Defined Variables SQL pattern matching enables you to use _ to match any single character and % to To find names containing exactly five characters, use five instances of the _ pattern character: To demonstrate how extended regular expressions work, the LIKE queries  Here is the query to use RLIKE/REGEXP pattern and display records whenever a column value starts with 2 vowel letters − mysql> select *from DemoTable664 where CityName RLIKE '^[aeiouAEIOU][aeiouAEIOU]';


If both $phonenumber and your string column could (potentially) both have the troublesome characters, this is hard to solve with regular expressions.

where replace(replace(replace($phonenumber, ' ', ''), '-', ''), '+') = replace(replace(replace(phonecol, ' ', ''), '-', ''), '+')

Pattern Matching with Regular Expressions, Solution Use the REGEXP operator and a regular expression pattern, described in … For example, you can easily write a SQL pattern %abc% to find strings that contain abc The following expression matches values that contain any hexadecimal digit character: But as the query result shows, the pattern doesn't work. There are two tables. One (definitions) with a field in which are stored the regular expressions and the other (cases) with a field on which to use these regexp. Working Example with Mysql Query Browser: set @RE = '^08' select * from cases where caseno rlike @RE My intention is to get the id from definition for later joining both tables.


I solved this with using replace() in query. I removed special characters from variable $phoneNumber (not through sql but before passing varable to sql) and compared it with the string result of the replace. SELECT phone FROM myTable where REPLACE(REPLACE(REPLACE(phone, ' ', ''), '+', ''), '-', '') = $phoneNumber;

SQL LIKE query for matching patern records with wildcard MySQL , SQL LIKE By using LIKE query we can match part of the full data present in a column. Read how Regular expression is used to Pattern matching More on getting records from table with different combinations of commands I have an EXEC that take all my contact list from my Database and put them into a variable. The second way to assign a value to a variable is to use the SELECT statement. In this case, you must use the := assignment operator because, within the SELECT statement, MySQL treats the = operator as the equal operator.


Rethink the overall design.

Cleanse phone numbers before putting them into the table. Then that SELECT, and the other SELECTs that are yet to be written, will be simple.

MYSQL Regular Expressions (REGEXP) with Syntax & Examples, Let's modify our above script and use the NOT charlist and see what results we will get after executing our query. SELECT * FROM `movies`  The syntax for assigning a value to a SQL variable within a SELECT query is @var_name := value, where var_name is the variable name and value is a value that you’re retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.


Managing & Using MySQL: Open Source SQL Databases for Managing , Open Source SQL Databases for Managing Information & Web Sites George Reese, return $pat; } The user's title word is escaped to make it work in an SQL query advanced feature of MySQL: it creates a regular expression and searches for so you cannot achieve any greater speed through the use of a bind variable. The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP_LIKE() function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE()).


MySQL, PHP | MySQL WHERE Clause · MySQL | BIN() Function · How to get ID of the last updated row in MySQL? Difference between MySQL and MS SQL Server  PERL - MySQL Query. Executing a query using the MySQL module is a two step process - very straight forward. We define a query in the form of a scalar variable then call upon that variable using our connection script and the query function.


MySQL REGEXP operator, MySQL REGEXP performs a pattern match of a string expression Example of MySQL REGEXP operator using(^) find from beginning. The following MySQL statement will find the author's name beginning with SELECT * FROM author WHERE aut_name REGEXP '^w'; Here no row has been fetched. Execute the SELECT query using the cursor.execute() method. Get resultSet from the cursor object using a cursor.fetchall(). Iterate over the ResultSet and get each row and its column value. Close the Python database connection. Catch any SQL exceptions that may come up during the process. python select from MySQL Table.