select statement using substring as WHERE condition

Question

How do i Select * from tableA where the condition is like this :

TableA:

|**Col A**  | **Col B** |
| Hello| 9*9 |
| World| 2*2 |
| Cat | 11*11 |

WHERE Col B contains same Number like '9*9'.

Col B is varchar (100).

I was thinking MAYBE using subString as the condition like :

Substring (1) = SubString (3)... but it didnt work out. Any other solution?

I don't exactly understand your question. Do you want to select the rows which columns for `Col B` contain the sub-string `"9*9"`? As such in this example it would be only the `Hello | 9*9` row? — Spencer Wieczorek, Mar 28 '17 at 02:10
there will be more of rows containing same numbers. Not just '9*9'. maybe '10*10' or '1*1'. I want to SELECT ALL THESE @SpencerWieczorek — user6308605, Mar 28 '17 at 02:13
[See this question](http://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words). Is that what you want or do you want to select the rows containing several different column values? In your question you specified *"[some] number"*, please clarify your question. — Spencer Wieczorek, Mar 28 '17 at 02:15
Not really, in other rows there are something like this in `col B` '1*10', '9*10'. As you can see here, the first and last number is NOT SIMILAR. I want to retrieve the one with first number and last number are SIMILAR like '8*8', '9*9' etc... @SpencerWieczorek — user6308605, Mar 28 '17 at 02:22

score 1 · Answer 1 · answered Mar 28 '17 at 02:32

1

select * from tableA
WHERE left(col_b, charindex('*', col_b) - 1) = right(col_b, len(col_b) - charindex('*', col_b))

I think this is what you need left, right and charindex

answered Mar 28 '17 at 02:32

beejm

1

MySQL doesn't have charindex. Use instr or locate instead – ysth Mar 28 '17 at 02:45

score 0 · Accepted Answer · answered Mar 28 '17 at 02:33

0

Check that the string is equal to its first part plus a '*' plus its first part again:

colb = concat(left(colb,length(colb)/2-1),'*',left(colb,length(colb)/2-1))

answered Mar 28 '17 at 02:33

ysth

2 Answers2