Formatting String data in an SQL Select Search [duplicate]

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
– GMB, Commented Dec 14, 2019 at 10:55
Does this answer your question? How to remove all non-alpha numeric characters from a string in MySQL? — Darth
– Darth, Commented Dec 14, 2019 at 11:08
This sounds like an X-Y problem: meta.stackexchange.com/questions/66377/what-is-the-xy-problem. — Gordon Linoff
– Gordon Linoff, Commented Dec 14, 2019 at 12:44

LukStorms · Accepted Answer · 2021-06-29 08:28:30Z

1

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.

edited Jun 29, 2021 at 8:28

answered Dec 14, 2019 at 11:32

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user9035386 · Accepted Answer · 2019-12-14 10:56:08Z

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, 2019 at 10:56

user9035386

2 Comments

Darth Over a year ago

Is there any way to implement regex for the illegal characters though?

user9035386 Over a year ago

This has already been asnwered here... stackoverflow.com/questions/6942973/…

Collectives™ on Stack Overflow

Formatting String data in an SQL Select Search [duplicate]

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related