Solved

select varchars starting by string with free ending

Posted on 2008-10-06
8
235 Views
Last Modified: 2012-05-05
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
Comment
Question by:boig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22648931
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
 

Author Comment

by:boig
ID: 22648973
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22648984
>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!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22648991
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
 

Author Comment

by:boig
ID: 22649004
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
 

Author Comment

by:boig
ID: 22649018
rtrim isn't working neither
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 22649039
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
 

Author Comment

by:boig
ID: 22649061
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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question