By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,293 Members | 1,382 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,293 IT Pros & Developers. It's quick & easy.

Truncate value

P: n/a


Hi,

I have a field in a table called tblFix, in this table I have a field
called Trailer,.... some of the value of this field is "EXTERN
Terms123456", I want to truncate this field and delete EXTERN Terms
from each one of them and update it with whatever numeric value, so I
want to truncate alpha chars and leave the numeric chars as it is.... I
know very simple UPDATE query but have no clue about this....I am a
novice to SQL... can anyone give some hints where I should start???

Apr 25 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
UPDATE tblTrades SET tblTrades.Trailer = Left( tblTrades.Trailer,13)
WHERE (((tblTrades.Trailer) Like "EXHAUST*") AND
((tblTrades.TradeDateTime)=Date()) AND
((tblTrades.OpposingBroker)="NITE"));
I tried this one but it's not working.... No sure what I am doing wrong
here....

Apr 25 '06 #2

P: n/a
UPDATE tblTrades SET tblTrades.Trailer = Left( tblTrades.Trailer,13)
WHERE (((tblTrades.Trailer) Like "EXHAUST*") AND
((tblTrades.TradeDateTime)=Date()) AND
((tblTrades.OpposingBroker)="NITE"));
I tried this one but it's not working.... No sure what I am doing wrong
here....

Apr 25 '06 #3

P: n/a
When the data is in the "extern" format is it ALWAYS that format?

Is it always "Extern Terms" and then the #?

Ron

Apr 25 '06 #4

P: n/a
No, sometimes. Most of the time data has 6 digit numeric value.

I tried following update query but it's not working:

UPDATE tblFIX SET tblFIX.Trailer = Left( tblFIX.Trailer,13)
WHERE (((tblFIX.Trailer) Like "EXTERN*") AND
((tblFIX.FIXDateTime)=Date()) AND ((tblFIX.Broker)="XXXX"));

Apr 25 '06 #5

P: n/a

anyone would like to give any hint/advice/input??
Hitesh Joshi wrote:
No, sometimes. Most of the time data has 6 digit numeric value.

I tried following update query but it's not working:

UPDATE tblFIX SET tblFIX.Trailer = Left( tblFIX.Trailer,13)
WHERE (((tblFIX.Trailer) Like "EXTERN*") AND
((tblFIX.FIXDateTime)=Date()) AND ((tblFIX.Broker)="XXXX"));


Apr 26 '06 #6

P: n/a
Hitesh Joshi wrote:
anyone would like to give any hint/advice/input??
Hitesh Joshi wrote:
No, sometimes. Most of the time data has 6 digit numeric value.

I tried following update query but it's not working:

UPDATE tblFIX SET tblFIX.Trailer = Left( tblFIX.Trailer,13)
WHERE (((tblFIX.Trailer) Like "EXTERN*") AND
((tblFIX.FIXDateTime)=Date()) AND ((tblFIX.Broker)="XXXX"));


I recommend creating a Public User-Defined Function (UDF) to filter out
the non-digits.

Public Function DigitsOnly(varIn As Variant) As Variant
Dim varTemp As Variant
Dim strChar As String
Dim I As Integer

DigitsOnly = Null
If Len(Nz(varIn, "")) = 0 Then Exit Function
varTemp = Null
For I = 1 To Len(varIn)
strChar = Mid(varIn, I, 1)
If IsNumeric(strChar) Then
varTemp = varTemp & strChar
End If
Next I
DigitsOnly = varTemp
End FunctionstrTemp
End Function
Here's a recursive version:

Public Function DigitsOnly(ByVal varIn As Variant) As Variant
DigitsOnly = Null
If Len(Nz(varIn, "")) = 0 Then Exit Function
If Len(varIn) = 1 Then
If IsNumeric(varIn) Then
DigitsOnly = varIn
Else
DigitsOnly = Null
End If
Else
DigitsOnly = DigitsOnly(Left(varIn, Len(varIn) - 1)) &
DigitsOnly(Right(varIn, 1))
End If
End Function
qryDigitalFilter:
UPDATE tblFIX SET tblFIX.Trailer = DigitsOnly(tblFix.Trailer) ...

Note that the IsNumeric function will only be True for digits when only
one character is used. The functions will replace a ZLS with Null and
leave Nulls alone. Add an extra few lines if you want to preserve
ZLS's. I didn't test to see if preserving ZLS's using the code below
breaks anything or not.

If Len(Nz(varIn, "")) = 0 Then
DigitsOnly = varIn
Exit Function
End If

Hope this helps,

James A. Fortune
CD********@FortuneJames.com

May 1 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.