Char to int

• joe joe-152023

  SSCommitted

  Points: 1819

  More actions

  December 15, 2005 at 2:54 am

  #91042

  "Do not attempt to construct, for example, binary values and convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between releases of SQL Server." - BOL Convert

  How do I find values over 7000 in a char column with values like '2AA' or '45B' or '5000BM'

  There are allways a number first and then one or two letters.

  Regards Joejoe

   

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  December 15, 2005 at 1:47 pm

  #609877

  declare @a varchar(20)

  select @a = '122aa'

  select CONVERT( INT, SUBSTRING( @a, 1, patindex( '%[A-Z]%', @a ) - 1 ) )

  Filter the statement with WHERE col LIKE '[0-9]%' so that it only affects rows that begin with number.