0

I have a table with column mapping which store record: "IV=>0J,IV=>0Q,IV=>2,V=>0H,V=>0K,VI=>0R,VI=>1,"

What is the sql to check whether or not a substring is in column mapping. so, I would like this:

  • if I have "IV=>0J" would return true, because IV=>0J is exact in string "mapping"
  • if I have "IV=>01" would return false. And so on...

I try this:

SELECT * FROM table WHERE charindex('IV=>0J',mapping)

But when I have "IV=>0", it returns TRUE. But, it should return FALSE.

Thank You..

Improve this question
6
  • 3
    Why not use WHERE mapping LIKE '%IV=>OJ%? Commented Jul 4, 2013 at 8:37
  • @Philips charindex searches an expression for another expression and returns its starting position if found. Commented Jul 4, 2013 at 8:38
  • @Aleks: It doesn't work, because if '%IV=>O%' it's also true. But, i want it FALSE. I just want it will return TRUE when the substring exact match in main string. Commented Jul 4, 2013 at 8:39
  • 2
    This is a bad design. You are using a column to store what should be rows in a separate table. Short of using regular expressions to parse the string, with the obvious performance hit, you can't avoid false positives. You COULD search for IV=>0, but it's still bad relational design. LIKE or CHARINDEX can't take advantage of any indexes too, which means that your query will result in a full table scan Commented Jul 4, 2013 at 8:40
  • 1
    Start by not denormalising your data into a comma seperated list. Commented Jul 4, 2013 at 8:45

5 Answers 5

Reset to default
4

You can search with commas included. Just also add one at beginning and end of mapping:

SELECT * FROM table WHERE charindex(',IV=>0J,',',' + mapping + ',') <> 0

or

SELECT * FROM table WHERE ',' + mapping + ',' LIKE '%,IV=>OJ,%'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

This should do the trick:

SELECT * FROM table
WHERE
    mapping LIKE '%,IV=>0J,%'
    OR mapping LIKE '%,IV=>0J'
    OR mapping LIKE 'IV=>0J,%'
    OR mapping = 'IV=>0J'

But you should really normalize the database - you are currently violating the principle of atomicity, and therefore the 1NF. Your current difficulties in querying and the future difficulties with performance that you are about to encounter all stem from this root problem...

Improve this answer

Comments

1

While you can search by including a comma in the string, this is a bad design for several reasons.

  • You are unable to take advantage of indexing
  • You force a full scan of the table, which will lead to bad performance AND excessive blocking.
  • You have to make sure that there is always a leading or a trailing comma (depends on what you expect in your LIKE expression).
  • You are no longer able to edit a single entry, you'll have to replace the entire string each time you want to change even a single mapping.
  • You open yourself to a concurrency nightmare if more that one users try to update different mappings that just happen to be stored in the same column.
  • Your table isn't even in 1st normal form any more, which is why you have such difficulties

You should normalize your mapping column, by extracting the data to a different mapping table, with at least the From and To columns you require. You can then add these columns to an index an convert your query using only a single index seek.

You can also add the ID values of your source table to the Mappings table and the index. This will allow you to convert the lookup for a source row to a join between the two tables that takes advantage of indexing

Improve this answer

Comments

0

charindex returns the position of the text, not Boolean.

to check if the text exists, compare to 0:

SELECT * FROM table WHERE charindex('IV=>0J',mapping) <> 0
Improve this answer

1 Comment

Yes you're right. But, here I just assumed in my code if the records > 0 return TRUE, otherwise FALSE. It doesn't matter.
0

I think you're missing something here, the Charindex function does not return TRUE or FALSE. It returns the starting point of the substring inside master string, or if the substring is not present, then -1. So you query should read,

SELECT * FROM table WHERE charindex('IV=>0J',mapping) > 0
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.