how to modify a data by removing a protion of it

[isonlyme]

hi

I have a field that contains this data :

12345.0

I want to get rid everything after the DOT

12345

the field is a nvarchar 40

how can i achive this?

Thank you

 

[genomon]

Update TableName Set FieldName = Substring(Fieldname,1,Len(FieldName-2)) oughta do it, although never worked with nvarchar (unicode) before...

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[mojoT]

Just out of curiosity, when you said you want to get rid of everything after the dot, did you mean update the table, or as a select? I'm assuming it's update.

genomon's way should work, although it assumes there is always one digit after the decimal, so if it is, then go with that.

If however you have varying numbers of digits to the right of the decimal, try using the CHARINDEX and LEFT functions. More info in BOL, but essentially, it checks a string for a specified character - in this case '.' - and returns the position of that character in the string.

You can use this by finding the position of the '.' and getting the string to the left of it.

Because the integer returned includes the '.' itself you minus one as follows:

UPDATE TableA
SET ColA = LEFT(ColA, CHARINDEX('.', ColA) - 1)

The above itself also assumes there is always a '.'. If it comes across a value without one, it returns -1 and you'll get an "Invalid length parameter" error. If this is being used for UPDATE, then I'd add a where clause as follows:

WHERE CHARINDEX('.', ColA) > 0

If however, you're using it for a select statement, using the where clause will not include any values without '.', so you'd be best to use a case statement I think.

SELECT  CASE
          WHEN CHARINDEX('.', ColA) > 0
            THEN LEFT(CharIndexValue, CHARINDEX('.', ColA) - 1)
          ELSE ColA
        END AS ColA
FROM TableA

Hope this helps.

 

[earthandfire]

If the field always contains a valid number then surely:

declare @num nvarchar(40)

set @num = '12345.0'

print [b]cast(cast(@num as numeric) as nvarchar(40))[/b]

would do what you need, without having to get involved with string manipulation


Hope this helps.

 

[earthandfire]

A bit of testing shows that just:

cast(@num as numeric)

is sufficient (assuming that the field contains valid numbers).


Hope this

 

[isonlyme]

ok, the data that should be there are telephone numbers

(123)123-3454 etc... and extentions 3434 etc..
the problem was tha the extensions when converted they look like this

3434.0

so i need to get rid of only the records that have .0 so i came up with this from all your suggetsions

update Tablename set Myfield= substring (Myfield,1,14) where Myfield like '%.0'

I dont know yet if it will work i havent test it, what u think?