Upper / Lower case using SQL

[Former Member]

Hello friends,

i have to read a field out of a dbtable. The field contains e.g. a string like "a11B". During the sql selection i would like to use upper or lower case to transform the string to "A11B":

SELECT...WHERE UPPER(attribute) IN range_variable.

Can somebody say how this command looks like in abap?

I haven't found a command for abap like UPPER() or UCASE() using mysql.

<<help>>

daniel

[Former Member]

Hi

You cannot use upper/ lower case in SQL statements .

Instead you will have to select and then validate transalating to upper or lower case .

Cheers

[NemanjaSimovic]

Hi,

Apparently you can but you have to be on 7.51 ABAP version.

Here is the example from SAP: https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abensql_function_upper_abexa.htm

Regards,
Nemanja

[Former Member]

You should first TRANSLATE (attribute) TO UPPER CASE then pass it to the SQL statement.

[Former Member]

Thx for your answers.

If i transalte first the attribute to upper or lower case, i will get either "A11B" or "a11b" but the information of the dbfield is "a11B" and nothing will be selected.

[Former Member]

put the data first into an internal table.

then loop at the internal table, and find the attribute... something like this...

select.... into corresponding fields of table i_tab....

loop at i_tab.

translate i_tab-attribute to uppercase.

...do something...

endloop.

[Former Member]

See my earlier reply.

Fisrt select from db table ( based on other restrictions) in an internal table .

Then translate both fields from Db and the field in your work area to Uppercase and then compare the strings to validate .

From database you will get more records than what you desire which you can delete from internal table.

Cheers

[Former Member]

ok thx, what a shame that i can't use only a select. now i will use a internal table.

daniel

[athavanraja]

if native sql has command sequence for select .. upper() then you could use the same in ABAP context.

check out the key word documentation of

EXEC SQL

Regards

Raja

[Former Member]

Depending on you database system, you can use native SQL. For Oracle:

report ztest no standard page heading.
tables lfa1.
data: name like lfa1-name1 value 'TEST'.
EXEC SQL.
  SELECT *
  INTO :LFA1
  FROM  LFA1
  WHERE UPPER(NAME1)  =  :NAME
ENDEXEC.

This will select where the name is "Test" or "TEST" or other combinations.

Rob