asked on

select varchars starting by string with free ending

I have a table with a varchar(8) column and I want to get all values from that column starting with '0709' and finishing with any value. Using LIKE '0709%' seems to do the job, but just work if the value is 8 chars long (for example, '070901' isn't selected). Using '%0709%' is not an option because '20709' is selected and it shouldn't.

My query is:
SELECT A.CENTROCOSTE
FROM __ASIENTOS AS A
WHERE A.CENTROCOSTE LIKE '0709%'

Guy Hengel [angelIII / a3]

what about this:

SELECT A.CENTROCOSTE
 FROM __ASIENTOS AS A
 WHERE  A.CENTROCOSTE LIKE '0709[0-9][0-9][0-9][0-9]'

Open in new window

boig

ASKER

Not working; I thing mssql is padding something in front of the varchar if it is shorter than 8 chars.
The values I have to get are:
070901,
070902,
....

I think this because using '%0709%' works, but also selects '207093' which I don't want to be selected

Guy Hengel [angelIII / a3]

>Not working; I thing mssql is padding something in front of the varchar if it is shorter than 8 chars.
no. mssql will only pad at the end with spaces if the data type is CHAR (<> VARCHAR).

so, you must be missing something else.

Guy Hengel [angelIII / a3]

so, you might try this:

SELECT A.CENTROCOSTE
 FROM __ASIENTOS AS A
 WHERE  rtrim(A.CENTROCOSTE) LIKE '0709[0-9][0-9][0-9][0-9]'

Open in new window

boig

ASKER

I have just tested and A.CENTROCOSTE LIKE ' 0709%' (two front spaces); so maybe mssql or maybe the program using mssql (I have to get data from excel from a mssql which is stored by a 3rd program).
Is there something similar to the one you wrote before but with spaces? something similar to:
[any_spaces]0709[any_char]

boig

ASKER

rtrim isn't working neither

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

boig

ASKER

The final solution was using '%', not using '[0-9]'; but you pointed the right direction using rtrim and ltrim.

Thanks

SELECT A.CENTROCOSTE
 FROM __ASIENTOS AS A
 WHERE  ltrim(rtrim(A.CENTROCOSTE)) LIKE '0709%'

Open in new window