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

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 ?


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

         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

Need Your Help

how to get access mdb table description note ? (via c++)

c++ ms-access

in Microsoft access application when we right click on one table a context menu show up, then click properties memu item a dialog will show the description of the table. My question is how to get t...

Handling $_POST in Wordpress

wordpress-plugin wordpress

How can i handle $_POST in wordpress? I am creating a custom "send" button which displays in my plugin's admin panel. When pressing the "send" button, mails have to be send to the subscribers. But ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.