Solved

select varchars starting by string with free ending

Posted on 2008-10-06
8
225 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
Industry Leaders: 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

Industry Leaders: 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!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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