Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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%'
0
boig
Asked:
boig
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
boigAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
boigAuthor Commented:
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]
0
 
boigAuthor Commented:
rtrim isn't working neither
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok:
SELECT A.CENTROCOSTE
 FROM __ASIENTOS AS A
 WHERE  ltrim(rtrim(A.CENTROCOSTE)) LIKE '0709[0-9][0-9][0-9][0-9]'

Open in new window

0
 
boigAuthor Commented:
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

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now