CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user5008894
user5008894

Reputation:

What is the most efficient way to search for a string regardless of spacing using Microsoft SQL

Currently I am using the if statement and listing all possible combinations. 

IF string = 'L1' OR string = 'L 1' then do something.

I wonder if there is a better way? I know I can use the replace function but is that gonna be efficient to do on 100,000+ lines in a database. It is being used in a scalar-function

Upvotes: 0

Views: 81

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19184

If you have a lot of combinations of words to look for and you can deal with some latency then consider using SQL Full Text Search: https://msdn.microsoft.com/en-us/library/ms142571.aspx

It's very easy to impact performance negatively with UDF's

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If you have an index on string, then:

where string in ('L1', 'L 1')

can make use of the index and will be the most efficient method.

Otherwise,

where replace(string, ' ', '') = 'L1'

seems the most convenient.

I would, however, suggest that you fix the string in the database. In fact, you can use a computed column and add an index on it:

alter table t add stringNoSpaces as (replace(string, ' ', ''))

create index idx_t_stringNoSpaces on t(stringNoSpaces)

Then the condition:

where stringNoSpace = 'L1'

will use the index and be happily efficient. And, SQL Server will maintain the index whenever the string column is modified.

Upvotes: 4

Related Questions