Formatting String data in an SQL Select Search

Question

I'm honestly not sure how to phrase this. So I'm just going to explain the issue

In order to find as many possible matches as possible, I currently run a piece of code that converts data to upper case, removes spaces and invalid characters. For example:

Hi there, I'm very happy! -> HITHEREIMVERYHAPPY

My database is reaching a size where it's no longer efficient to run the piece of code that checks each line in a ResultSet, so I was wondering if this is possible in SQL somehow?

SELECT * FROM Data WHERE Input.replaceAll("[^a-zA-Z0-9]", "").replace(" ", "").toUpperCase() = INPUT AND Response.replaceAll("[^a-zA-Z0-9]", "").replace(" ", "").toUpperCase() = RESPONSE

Please tag your question with the database (and version) that you are using: mysql, oracle, sql-server... String functions are quite vendor-specific. — GMB, Dec 14 '19 at 10:55
Does this answer your question? [How to remove all non-alpha numeric characters from a string in MySQL?](https://stackoverflow.com/questions/6942973/how-to-remove-all-non-alpha-numeric-characters-from-a-string-in-mysql) — Darth, Dec 14 '19 at 11:08
This sounds like an X-Y problem: https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem. — Gordon Linoff, Dec 14 '19 at 12:44

LukStorms · Accepted Answer · 2021-06-29T08:28:30.727

You might like RLIKE for this.

-- Sample data
create table Data
(
  Id int primary key auto_increment,
  Input varchar(100),
  Response varchar(100)
);
insert into Data (Input, Response) values
  ('Hi there, I''m very happy! ->', 'To have 1 solution.')
, ('HITHEREIMVERYHAPPY', 'ToHave1Solution')
, ('I rlike this', 'IRLikeThis')

Query:

SELECT *
FROM Data
WHERE Input RLIKE '[^ A-Za-z0-9]'
  AND Response RLIKE '[^ A-Za-z0-9]'

Result:

Id | Input                        | Response           
-: | :--------------------------- | :------------------
 1 | Hi there, I'm very happy! -> | To have 1 solution.

db<>fiddle here

It basically uses the regex [^ A-Za-z0-9], which matches strings that contain any character that's not a letter, digit or space.

score 0 · Answer 2 · answered Dec 14 '19 at 10:56

0

Just do a replace of the spaces...

https://www.w3schools.com/sql/func_sqlserver_replace.asp

SELECT * FROM TABLE WHERE REPLACE(TABLE.COLUMN,' ','') = VALUE;

answered Dec 14 '19 at 10:56

Is there any way to implement regex for the illegal characters though? – Darth Dec 14 '19 at 11:03
This has already been asnwered here... https://stackoverflow.com/questions/6942973/how-to-remove-all-non-alpha-numeric-characters-from-a-string-in-mysql – Dec 14 '19 at 11:05

Formatting String data in an SQL Select Search

2 Answers2