SELECT with WHERE clause on alphanumeric values

[Former Member]

I have a table that has an column id of the form D1,D2,D3...

I want to select the rows based on:

SELECT * FROM "SAP_ECC"."PARSONSF"."VBRP"

WHERE "VBELN" >= 'D10' AND "VBELN" <= 'D100'

to retrieve D1,D2,D3,D4...D100

But I only get D1,D10,D100.

Any ideas how I can do this?

Thank you!

[former_member285534 · Accepted solution]

Hi Frederick,

As you state in the discussion's title, the problem is precisely that the search in the WHERE clause is alphanumeric so the internal ordering of the values aren't working as expected.

Thus, the solution is to rewrite your search condition so that SQL works on numerical values instead of alphanumerical.  One way to achieve this is to remove the first letter (D) from the low and high values, and then convert them to a numerical type.

That is:

SELECT * FROM "SAP_ECC"."PARSONSF"."VBRP"

WHERE TO_INT( SUBSTR_AFTER ( "VBELN", "D" ) ) >= 10 AND TO_INT( SUBSTR_AFTER ( "VBELN", "D" ) )<= 100

You can also try with this more simple version, where I think automatic type conversion will happen

SELECT * FROM "SAP_ECC"."PARSONSF"."VBRP"

WHERE SUBSTR_AFTER ( "VBELN", "D" ) >= 10 AND SUBSTR_AFTER ( "VBELN", "D" ) <= 100

SUBSTR_AFTER is a string function that works on a target value ("VBELN"), returning the text immediately after the target pattern (in this case "D").

Right now I have no access to a HANA environment where I can test the solution.  I hope I didn't make a typo.  But the idea is there.  Surely it will work !

Hope this helps.  If so please mark my comment as correct answer so I can get my first scn points  😃

Fernando

[Former Member · Accepted solution]

Hi Fernando,

What a great response! Thank you for taking the time to write up such a detailed answer.

Fred

[former_member285534 · Accepted solution]

You're welcome.  I'm glad that helped !

Fernando