Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DeadHead
2 Posts |
 Posted - 2009-04-10 : 16:27:54
|
| I'm sorry if i'm posting in the wrong forum but my question is for straight SQL code running on an iSeries. It involves the where clause. My users just can't remember to left justify our company number in the below example. When putting in the CO#, they don’t like remembering to left justify the CO#’s less than ‘100’…like ‘75’, etc. When they come into the SQL, they may find ‘100’ still in the field and just put a ‘ 75’ instead of ‘75’ and it finds nothing then. I guess since they are numbers, they keep thinking that it should be right justified within the quotes but it's a 15 byte character filed and needs to be left justified inside the literal. I've tried the "TRIM" and STRIP functions but they don't work in the WHERE clause. Here are some examples I've tried that didn't work....WHERE TRIM(LOC)=' 75'WHERE LTRIM(LOC)=' 75'WHERE STRIP(LOC,L,' ')=' 75'WHERE STRIP(LOC,B,' ')=' 75'What is it with users...all they need to do is put it like '75'.Thanks in advance for any help you may be able to provide.Ken |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-04-10 : 18:46:14
|
TRIM and STRIP are not T-SQL functions.If you want to strip out leading and trailing spaces, the functions are LTRIM and RTRIM, respectively.For example:ltrim(rtrim(LOC)) = '75' I think the comparison operator ignores trailing spaces, so you may not even need the rtrim function.This assumes that you are using T-SQL on a MS SQL server. It is not clear from your post whether you are. |
 |
|
|
DeadHead
2 Posts |
Posted - 2009-04-13 : 12:28:47
|
| Thanks for replying... I've tried the different trim functions but to no avail. The SQL runs on an iSeries box. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-04-13 : 14:06:27
|
| No idea what an "iSeries" box is. No idea what "The SQL" is. What is your database software?SQL Server?MySQL?Oracle?[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
|
|
SQL code