-1

I have an age column whose data type is VARCHAR, and in that column the values of age are written like this, 20. 30. 40. 50. Want to remove this '.'

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Student
  • 1
  • How do I remove this . from my age values? – Student Aug 04 '22 at 19:13
  • 1
    You can use a combination of SUBSTR with RIGHT and REVERSE. See these: [SUBSTR](https://www.w3schools.com/sql/func_sqlserver_substring.asp), And this one: [RIGHT and REVERSE](https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server) – Pam Stums Aug 04 '22 at 19:19
  • 5
    `age` is a number. data types matter. Don't use VARCHARs to store numeric information. `20.` would be a literal to store a `DECIMAL(2,0)` . Casting to DECIMAL or to NUMERIC - or even to INTEGER - should help. And: don't store `age` if you don't have an important reason to do so. It will be incremented by 1 next year - and that's nothing to be stored permanently. – marcothesane Aug 04 '22 at 19:22
  • 1
    @PamStums why so complex? `REPLACE()` will work fine. But as mentioned already, age should be a number and in (nearly) all cases not be stored at all – HoneyBadger Aug 04 '22 at 20:53
  • My intention was "preventive" coding. I meant to find the last "dot". and cover the case where - accidentally - the value is something like 30.5. (just because the mention type is string which anything can be there). (and assuming changing the type is not our first choice) – Pam Stums Aug 04 '22 at 21:04
  • If you have a value like 30.5 then what result do you want? Leave it as it is (because . is not the last character), return 30 (because you want to remove everything from the . onwards) or something else? Are there any other patterns of data that you haven’t mentioned but you still want to handle? – NickW Aug 04 '22 at 21:17
  • Is it relevant to know how it got there in the first place or why it's stored as a character value? – shawnt00 Aug 05 '22 at 06:16
  • 1
    Welcome to SO! Please [edit] your question and add information about the RDBMS you're using (PostgreSQL, MS SQL Server, Oracle, ...), as well as example input and expected output (e.g. it's unclear what should happen with '30.5' - you said you only want to remove the dot, but presumably, you want to get rid of the fractional part altogether) – Frank Schmitt Aug 05 '22 at 06:19

1 Answers1

0

It would be best to have age as in number as marcothesane points out.

However if you just want to get rid of the dot and anything after it (perhaps because your worried about changing the datatype breaking something else where it is referenced as a varchar) then..

Assuming MSSQL

You could use:

UPDATE [YourTable] SET age = LEFT(age,charindex('.',age)-1) FROM [YourTable]

..after that, assuming all the values are numeric which you can check by:

SELECT * FROM [YourTable] where isnumeric(age) =0

..and checking it returns no results

..you can change the column to an integer by using

ALTER TABLE [YourTable] ADD age2 INT

..to add a new integer column

UPDATE [YourTable] SET age2 = CAST(age AS INT)

..to put in the values as numbers from the existing column to the new one

ALTER TABLE [YourTable] DROP COLUMN age

..to get rid of the original age column

EXEC sp_rename 'dbo.testtable.age2', 'age', 'COLUMN'

..to rename the new column to replace the original.

Hope this helps

Kind Regards

Kev

Kev
  • 1
  • 1