MySql question

[robotninja3]

Hello people!

 

im begginer in this mysql world, and perhaps my question is very basic and easy to solve, but i really can use some help here ...........

 

my problem: i have a database in wich the data is stored padded with blank space before the text... how can i build my query to get an answer from the server?? example:

 

CUST_KEY          --> field_name

    1                    --> the data with blank space

    12

  95447

874591

    2

    72

  95448

 

the data is stored in the database with a number of spaces depending of the lenght of the number typed inside... i just dont know how to make my query welll, i hope anyone here can bring some light to me.

 

i read something about LTRIM and RTRIM... also LPAD and RPAD .... but i dont know if these functions may help me with this problem.

 

 

sorry for the bad english.

 

Rn

[analog]

LTRIM removes leading spaces and RTRIM removes trailing spaces. You can also use TRIM which removes both leading and trailing spaces. More information: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

 

To remove only leading spaces you could do:

 

SELECT
*
FROM
table
WHERE
LTRIM(CUST_KEY) = '95447'

 

It might be faster to add the spaces to the search string instead of removing the spaces from the column depending on if you know how many to add.

 

Is there a reason for storing the data with leading spaces?

[robotninja3]

Hello and thank you for the answer, analog.

 

The reason of the blank spaces in the DB is because the tables are part of a ERP, so the system stores the data of this field with leading and trailing spaces... my app is an external interface to that system, so i must adapt the code to the stored information.

 

the number of spaces are 4 if the number has one digit, and the spaces decreases as increases the digits of the number stored in the field:

 

    1 -> four spaces

  23 -> three spaces

  957 -> two of them

1492

12345 -> no space

 

but i dont know the true reason of this design... maybe the blanks help in the internal report presentation?

 

i will try your suggest in the night and the i will share the results here, ok?

 

see you later, analog.