StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSELECT Statement with substr in WHERE Clause
text
Body
copied!<p>I have here sample table with three fields.</p> <pre><code>TableA FieldA FieldB FieldC ====================================== 123XYZ456 XYZ John 124NNN333 NNN Jenny 232XPT124 XPT Jade 456XXX345 XXX James </code></pre> <p><code>FieldA</code> has a fixedlength of 9. I didn't design this table and some applications are already using it.</p> <p>I want to select <code>FieldB</code> and <code>FieldC</code> with conditions against <code>FieldA</code>.</p> <p>Using this sql statement:</p> <pre><code>SELECT FieldB, FieldC FROM TableA WHERE FieldA LIKE Concat(@paramA, '%', @paramB) </code></pre> <p>I cannot achieve my desired result. When I try to search with paramA value of <code>12</code> and paramB value of <code>''</code>, I get the <code>2</code> results:</p> <pre><code> FieldA FieldB FieldC ====================================== 123XXX456 XXX John 124XXX333 XXX Jenny </code></pre> <p>because obviously it matches with <code>12%</code> and that is not what I want. I want that the params should match the correct index of the string.</p> <p>If I search for <code>paramA = '12'</code> and <code>paramB = ''</code> then it should have no result. To get the fields (<code>FieldB</code>, <code>FieldC</code>), I need the correct values of <code>paramA = '123'</code> and <code>paramB = '456'</code> so it will return <code>XYZ</code> and <code>John</code>. If I want to return <code>James</code> then i have to give <code>paramA = '456'</code> and <code>paramB = '345'</code></p> <p>How could I correctly build a SQL Statement for this? Any idea? Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload