Do we have any equivalent function in SQL SERVER for VAL() function of MS Access?

Question

I have got a problem while running the code Access Query directly in sql server which contains VAL([CoLUMN_NAME]). After googling alot I found a CAST(COLUMN AS DATATYPE) to replace VAL().

But When that column is Text type and if we write VAL([COLUMN_NAME]) we are getting only the number part from that text. For Example the column has this value 45-A45 and used VAL([COLUMN_NAME]) we will get only "4545".

If we want to achive this in SQL SERVER how to do ?

Answer 1

Val is just the numbers to the left of a string, so you could start with something very roughly like:

SELECT CASE 
         WHEN Patindex('%[^0-9]%', table_1.atext) > 0 THEN Cast( 
         LEFT(table_1.atext, Patindex('%[^0-9]%', table_1.atext) - 1) AS INT) 
         ELSE 0 
       END AS Val 
FROM   table_1; 

This would return 45 instead of "45 dozen", but needs to be much improved to return 45 instead of "45".

Edit re comments

@Andriy M's solution (below) works exactly like Val, as far as I can tell.

  SELECT CAST(LEFT(table_1.atext, Patindex('%[^0-9]%', table_1.atext + 'x') - 1) 
  AS INT) AS Val from table_1 

Edit #2

Table from MS Access:

AText   Val(Atext)  
45 dozen    45
ABC 45      0
45_ABC      45
45 ABC 34   45
45          45
ABC         0

Using @Andriy M's solution you get exactly the same result.

I have since found that @GMastros has a better solution

CAST(LEFT(atext, Patindex('%[^-.0-9]%', atext + 'x') - 1) AS Float) AS Val

Note that I changed the search pattern to include the negative sign and a decimal separator. I also changed the data type for the cast to a float. Val returns a vb6 double, which is the same as a SQL Server float. -- G. Mastros