The ltrim function requires 1 arguments

[jojo79]

trying to get rid of the leading zero's on my field Billed_Weight(nvarchar) with the following statement, with no lick


select LTRIM(Billed_Weight,'0');

I get the following error

The ltrim function requires 1 arguments.

 

[gmmastros]

LTrim is used to remove spaces. If you want to remove leading zeros, then you could simply convert to integer and return that instead.

select Convert(int, Billed_Weight)


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

What version of VFP?
In VFP9 the syntax is:

LTRIM(cExpression, nFlags, cParseChar)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jojo79]

only 1 problem with the convert, In the Field, it list looks like this

000025
000041
000120
000008
LTR
000011
000045
000077

the "LTR" means it was a letter and not weighted. Can you use the case command with convert??

 

[AlexCuse]

Something like this?

select case when isnumeric(BilledWeight) = 1
then cast(BilledWeight as Integer)
else BilledWeight END as NewBilledWeight
from MyTable

Hope it helps,

Alex

ps: Boris - FoxPro forum is third door on the left

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

In that case, you should conver to integer when you can convert to integer, otherwise, just return the data as is. Something like this should work for you.

[COLOR=blue]Select[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]IsNumeric[/color](Billed_Weight + [COLOR=red]'.0e0'[/color]) = 1 
            [COLOR=blue]Then[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Int[/color], Billed_Weight))
            [COLOR=blue]Else[/color] Billed_Weight
            [COLOR=blue]End[/color] [COLOR=blue]As[/color] Billed_Weight

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Weight just a second, you will have to do this actually (convert to integer, then back to varchar):

select case when isnumeric(BilledWeight) = 1
then cast(cast(BilledWeight as Integer) as varchar)
else BilledWeight END as NewBilledWeight
from MyTable

This is because each column must be just one data type. Better idea would be just store billed weight of 0 for letters, and keep the whole column as an integer imho.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

Here is another way by using LTRIM and REPLACE twice

[COLOR=blue]create[/color] [COLOR=blue]table[/color] GarbageData (v [COLOR=blue]varchar[/color](666))


[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000025'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000041'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000120'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000008'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'LTR'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000011'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000045'[/color])
[COLOR=blue]insert[/color] GarbageData [COLOR=blue]values[/color]([COLOR=red]'000077'[/color])


[COLOR=blue]select[/color] [COLOR=#FF00FF]replace[/color]([COLOR=#FF00FF]ltrim[/color]([COLOR=#FF00FF]replace[/color](v,[COLOR=red]'0'[/color],[COLOR=red]' '[/color])),[COLOR=red]' '[/color],[COLOR=red]'0'[/color])
 [COLOR=blue]from[/color] GarbageData

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

Alex, try adding 9d3 to your sample data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

O GOD!!!!
I thought I answer on VFP forum!
Sorry jojo79 for that stupid answer!

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

VFP what is that?

Is it this?

http://msdn2.microsoft.com/en-us/vfoxpro/bb308952.aspx

http://www.eweek.com/article2/0,1895,2103695,00.asp

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[AlexCuse]

hehe yeah, how could I forget that FAQs :-(

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Yes, Denis, that WAS it!


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP