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:
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
