Solved

select varchars starting by string with free ending

Posted on 2008-10-06
8
207 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
  • 4
  • 4
8 Comments
 
LVL 142

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 142

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
 
LVL 142

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL FUll Text Not returning any results 2 16
Help with SQL Server Stoplist 2 20
Need to update TableA to TableB 6 34
Help Parsing a String with SQL Syntax 23 28
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now