Truncate String field

[Hitesh]

Hi,

I have a string field. This unwanted chars only appears once in a while.
EXHAUST trade123456
EXHAUST trade789012
EXHAUST trade345678
EXHAUST trade901234

I want to truncate the frist 13 charctors "EXHAUST trade" without creating
new column.

Any hints for an update query.

Thank you,
Hitesh

 

[tina]

try

UPDATE TableName SET FieldName = IIf([TableName].[FieldName] Like
"E*",Right([TableName].[FieldName],6),[TableName].[FieldName]);

note that when running an update query for the first time, it's always
advisable to try it on a COPY of your database; if the update doesn't turn
out as expected, ditch the copy and try again on another COPY of the
db...until you get it working.

hth

 

[Marshall Barton]

I have a string field. This unwanted chars only appears once in a while.
EXHAUST trade123456
EXHAUST trade789012
EXHAUST trade345678
EXHAUST trade901234

I want to truncate the frist 13 charctors "EXHAUST trade" without creating
new column.

Use a calculated field in your query:

IIf(thefield Like "EXHAUST trade*", Mid(thefield,14),
thefield)

 

[John Vinson]

Hi,

I have a string field. This unwanted chars only appears once in a while.
EXHAUST trade123456
EXHAUST trade789012
EXHAUST trade345678
EXHAUST trade901234

I want to truncate the frist 13 charctors "EXHAUST trade" without creating
new column.

Any hints for an update query.

Yet a third option:

UPDATE fieldname
SET fieldname = Replace([fieldname], "EXHAUST trade", "")
WHERE fieldname LIKE "EXHAUST trade*";


John W. Vinson[MVP]

 

[Hitesh Joshi]

Thank you everyone. It worked!!

SET fieldname = Replace([fieldname], "EXHAUST trade", "")
WHERE fieldname LIKE "EXHAUST trade*";

I going with this one... but other two solutions worked too.

Hitesh

Hi,

I have a string field. This unwanted chars only appears once in a while.
EXHAUST trade123456
EXHAUST trade789012
EXHAUST trade345678
EXHAUST trade901234

I want to truncate the frist 13 charctors "EXHAUST trade" without creating
new column.

Any hints for an update query.

Yet a third option:

UPDATE fieldname
SET fieldname = Replace([fieldname], "EXHAUST trade", "")
WHERE fieldname LIKE "EXHAUST trade*";


John W. Vinson[MVP]