How to write query using LIKE to accept special characters also?

sql query to find special characters in string
sql check if string contains special character
remove special characters from string in sql query
how to escape special characters in mysql select query
sql server find non ascii characters
how to find special characters in sql oracle
special characters in sql query
remove special characters from string in sql server 2012

Am having one value in database as "123-456-789". My query is

SELECT * FROM table_name WHERE phone LIKE "%123456789%"

But it is not giving the result. Any idea

Of course ,you excluded the dashes so it does not match the middle part. The logic of your query is "anything before my string and anything after" but the middle part should be the same.

SELECT * FROM table_name WHERE phone LIKE "%123-456-789%"  

This will work.

But if you want to exclude dashes you can do something like:

SELECT * FROM table_name WHERE REPLACE(phone ,'-','') = "123456789"  

If your input includes also dashes then you can remove them as well:

SELECT * FROM table_name WHERE REPLACE(phone ,'-','') = REPLACE('123456789','-','')

That way you can search a number with dashes or without but dashes will never be taken under consideration.

SQL Server STRING_ESCAPE Function By Examples, is matching with regex object then search method returns a match object otherwise return None. The underscore matches only one character, so the results in the above query will only return 3-letter names, not a name such as 'John' The underscore matches at least one character, so the results do not include 'Jo' Try running the previous query using % instead of _ to see the difference. NOT LIKE

You could try using a regex directly within mySQL like this:

select * from `table_name` where `phone` regexp '[0-9]{3}-[0-9]{3}-[0-9]{3}';

SQL Wildcard Characters, such as the "at" sign (@), ampersand (& , and exclamation point (!). The LIKE condition allows you to use wildcards in the where clause of a SQL statement in Access 2003/XP/2000/97. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.


SELECT * FROM table_name WHERE `phone` LIKE '%[0-9]' '_' '[0-9]' '_' [0-9]%'

You might also consider some OR statements

SELECT * FROM table_name WHERE `phone` LIKE '%[0-9]' '_' '[0-9]' '_' [0-9]%' OR '%[0-9]%'

Program to check if a string contains any special character , Get MySQL Cookbook now with O'Reilly online learning. You want to write a quoted string, but it contains quote characters or other special characters, Learn the syntax rules that govern the interpretation of strings in queries. also must have any special characters escaped if you want to include them in a query string,  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more How to write query using LIKE to accept special characters also?

VALID Varchar Values – SQLServerCentral, You can, however, search special characters with advanced field queries (see Advanced Field Queries). Character, Action, Result behavior, Query example Matches exact term, Like the plus character, when the number sign (also referred​  Following is an Excel macro that encloses text (in each selected cell) in single quotes, replaces ' with '', and trims outside spaces, to prepare text for SQL insert statements. NOTE : to have macros always available in Excel, store them in a file named personal.xls in the XLStart folder.

Writing Strings That Include Quotes or Special Characters, Special characters are one of those necessary evils. Also, look at a number of the problems. Armed with that knowledge, let's write a query that will locate all of the strings that have a space at the beginning Let's take a moment to go over the 4 basic wild card expressions the LIKE operator provides:  Next, let's look at how you would use the Oracle NOT Operator with wildcards. Let's use the % wilcard with the NOT Operator. You could also use the Oracle LIKE condition to find suppliers whose name does not start with 'T'. For example: SELECT supplier_name FROM suppliers WHERE supplier_name NOT LIKE 'W%';

Using Special Characters in Queries - Coveo Platform 7, In this tutorial we look at how we can find special characters using regex with T-​SQL. In the first two queries, we look for any data row with one special character of an SELECT * FROM alphareg WHERE Alphabetic LIKE '[!] In addition, we can also check if we have a special punctuation character,  The Like criteria or operator is used in a query to find data that matches a specific pattern. For example, in our database, we have a "Customers" table, like the one below, and we want to locate only the customers living in cities whose names start with "B". Here’s how we’ll create a query and use the Like criteria: Open the Customers table: