How to check efficiently, if a substring exists : SQL Query

Question

I have to do certain actions based on the decision if a sub string exists in a column.

For example my column 'LangCodes' have # separated values like en-us#ar-ae#in-id.

I can use the SQL in operator if I can convert the value in form like : 'en-us','ar-ae','in-id'.

For example select Col1 from Table1 where 'en-us' in (LangCodes)

Do I need to use replace function of SQL to accomplish this or any better way exists?

How about SELECT * FROM TABLE_NAME WHERE LANGCODES LIKE '%en-us%' — drigoangelo
– drigoangelo, Commented Dec 27, 2013 at 14:01

Gordon Linoff · Accepted Answer · 2013-12-27 14:01:42Z

2

You cannot do this efficiently in SQL Server, because you are storing your data in a fashion not consistent with the use of relational databases. You need a separate correlation table that has columns id and LangCode, with one row per language code.

You can do what you want with string operations. Here is a typical way:

where '#'+LangCodes+'#' like '%#en-us#%'

This, however, cannot take advantage of an index on LangCodes.

answered Dec 27, 2013 at 14:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Biki Over a year ago

Thanks for the hint. It helped me to eliminate 3 OR conditions. i.e. : select Col1 from Table1 where LangCodes = 'en-us' OR LangCodes like '%#en-us#%' OR LangCodes like '%en-us#%' OR LangCodes like '%#en-us%'

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

The most efficient and best way to check your languages codes is to seperate them in your table.

Never, never, never store multiple values in one column!

This is how your tables could look like (just examples)

product table
-------------
id
name

language_code table
-------------------
id 
name

product_language_code table
---------------------------
product_id
language_code_id

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 27, 2013 at 14:00

juergen d

205k40 gold badges303 silver badges377 bronze badges

2 Comments

Biki Over a year ago

I understand that we should not store multiple values in one column, but what if these values are coming from a Legacy Database & we dont have any control on how to store these values?

juergen d Over a year ago

Then use Gordons answer

Collectives™ on Stack Overflow

How to check efficiently, if a substring exists : SQL Query

2 Answers 2

1 Comment

Never, never, never store multiple values in one column!

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Never, never, never store multiple values in one column!

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related