-2

I am trying to convert varchar to Int and i tried cast, convert, try_cast but getting an error conversion failed when converting varchar to datatype INT.Any other way to convert it?

select cast('123RA' as INT) as p
learner123
  • 37
  • 1
  • 7
  • you can have any non numeric characters in the text for the conversion to succed see https://stackoverflow.com/questions/18625548/select-query-to-remove-non-numeric-characters how to remove them – nbk May 08 '22 at 17:42

1 Answers1

1

We have to remove non-numeric characters to be able to cast to int.

SELECT 
textval,
cast(
  LEFT(SUBSTRING(textval, PATINDEX('%[0-9.-]%', textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(textval, PATINDEX('%[0-9.-]%', textval), 8000) + 'X') -1)
  as int) numval
from t
GO

textval | numval
:------ | -----:
123RA   |    123

db<>fiddle here

  • Thanks for your reply .But I need nonnumeric characters. – learner123 May 08 '22 at 17:35
  • then you must explain in your question the wanted result – nbk May 08 '22 at 17:43
  • This answer does what you need, what does "I need nonnumeric characters" mean? You are asking to convert the string to an integer. What do you expect?? – Stu May 08 '22 at 17:44
  • @learner123 so you want to cast non-numeric values to a numeric datatype, and you are surprised you can't? Do you know what datatypes are? – HoneyBadger May 08 '22 at 18:04