TRUNCATING VALUES IN A FIELD

[JUDGE27]

Hello all,

I have a column in a table with about 3000 rows. I need
to truncate the values in this column to remove the
first 3 values eg.

345753 = 753

Is there anyone who can help with this request.

Thanks in Advance.

 

[r937]

what datatype is the column? if it's character, use the substr function, but if it's numeric...?

rudy

http://rudy.ca/

 

[MeanGreen]

Say Col1 is your column with your data to trucate. Try this:

select substring(rtrim(cast(col1) as varchar(255)),3,datalength(rtrim(cast(col1) as varchar(255)))-3) as newcol1 from table

That would get what you need I believe even if it is an integer datatype. If it is character then just drop the casting and you should have it. Let me know if it doesn't work.

Good Luck!

 

[JUDGE27]

The Datatype is integer.

MeanGreen, the following statement returned:

Server: Msg 1035, Level 15, State 10, Line 1
Incorrect syntax near 'cast', expected 'AS'.

I am unfamiliar with the Casting function. Can
you please take a look at this.

Thanks in Advance!

 

[JUDGE27]

I have managed to modify the code supplied by MeanGrean,
and have it in a manageable state.

The following is the modified code.

select substring(cast([COLUMN HERE] as varchar(255)),4,10) as [NEW_COLUMN_NAME_HERE]
from [DATABASE HERE]

Thanks for you help guys!

 

[MeanGreen]

Sorry,
The starting position should be 4 and not 3. The datalength function would allow you to handle any character length while the rtrim will remove any padded spaces to the right. The modified code you have will work for you. One other thing you might want to do is CAST the substring as an integer so you have the same datatype that was input for output. Just a thought. Glad to see it worked for you.

 

[AKAK]

You could also do:

newvar = varname % 1000

which gives the remainder when divided by 1000


AK