Leading Spaces

[Holmer1]

I am trying to pass a value into an SQL statement to retrieve a result. The value I pass is from a varchar field and has a length of 12. The value I retrieve for a table for example is 3 . When I try and pass this value into my sql statement it gives me no results because the field is actually 12 characters in length but I am only passing the value of 3. When I assign the value ,3 , to a variable and get the length using the LEN command it shows 12 as the length but my sql statement is only seeing the value 3 instead of ' 3' . Any ideas on this??

 

[MDXer]

You can use RTRim(LTrim(ColumnName)) this will remove all leading and trailing spaces. If I have to trim for trailing spaces I always go ahead and trim for leading spaces.

If however you want just the first X characters then you can use the SubString() to specify the start and end points of the data you are trying to get.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[RileyCat]

Need a hair cut?

Maybe get a TRIM (L, R, or otherwise).

 

[Holmer1]

What I really ant to do is add the leading 11 spaces to the value returned , in this case 3, so that the field is 12 characters long and when I plug it into my sql statement I will ge the right results. I was hoping to use something that would say if this field is 1 character long then add 11 leading spaces, if 2 chars long then add 10 leading spaces etc. Whats confusing to me is that when I check the length of the variable it is 12 but when I write my sql statement to the screen it only shows up as '3' when I needed and expected it to show up as ' 3'

 

[RileyCat]

Where no man has gone before . . .

space(x), the final frontier.

 

[RileyCat]

right (space(x) + "var", x)

 

[TLeaders]

Or you could just replicate the leading zeros into the field.


declare @infield varchar (12)
declare @outfield varchar (12)
set @infield = ' 4'

set @outfield = (REPLICATE('0', 12 - DATALENGTH(ltrim(@infield))) + ltrim(@infield))

print @outfield

Try this an see if it is what you need.
Tom

 

[RileyCat]

Holmer,

If you really want to do this efficiently, you would be better off to trim both the input and the test string and then test on the results.

If you know that your string is going to be right or left justified, you only need to do a trim on the opposite side. If there is likely to be trailing and leading spaces, use the RTrim(LTrim) suggestion as above.

declare @strOne char(10),
@strTwo char(10)

select @strOne = " 123 "
select @strTwo = " 123"

select '*'+RTrim(LTrim(@strOne))+'*', /* Just to see what it looks like */
'*'+RTrim(LTrim(@strTwo))+'*' /* Again ... */


Good luck and just play around with it to see what it can do.

 

[Holmer1]

Is there anything else I can use in the place of Replicate?? I get a type mismatch when I do this.
Type mismatch: 'REPLICATE'

 

[TLeaders]

My mistake, I was using zeros to count the output field as I was writing this. As long as the output field is 12 characters is size the ltrim/rtrim combination would be the best solution.

Replicate should work. I cut the posted code from query analyzer.

Good luck.
Tom

 

[RileyCat]

I'm curious why you even want to use REPLICATE?

It seems to me there are other methods that have already been expressed that are more efficient in terms of memory and faster in terms of processing.

 

[Holmer1]

Yes , RileyCat, I think you are right. I have it working in my query analyzer but still have something wrong in my asp page. I have used the LTRIM on the variable so it is only 1 char long in this case. I passed this into my sql statement and used the LTRIM on my table column. eg. MCU= LTRIM(rs(2))

sql statement : select * from testing where Ltrim(GMMCU)= '"& MCU & "'

 

[Holmer1]

I have been successfull using a combination of the LTRIM and RTRIM commands within my result set and my sql query.
Thanks to RileyCat, Tleaders, and MDXer.