我们在SQL SERVER中是否具有与MS Access的VAL()函数等效的功能? [英] Do we have any equivalent function in SQL SERVER for VAL() function of MS Access?

问题描述

我直接在包含VAL([CoLUMN_NAME])的sql服务器中运行代码访问查询时遇到问题.大量搜寻之后，我找到了CAST(COLUMN AS DATATYPE)来替换VAL().

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().

但是，当该列为文本类型时，如果我们编写VAL([COLUMN_NAME])，则只能从该文本中获取数字部分.例如，该列的值为45-A45，并且使用VAL([COLUMN_NAME])我们将仅获得"4545".

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".

如果我们想在SQL SERVER中达到这个目的怎么办?

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

推荐答案

Val只是字符串左侧的数字，因此您可以从大致类似的内容开始:

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; 

这将返回45而不是"45打"，但是需要做很多改进才能返回45而不是"45".

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

编辑评论

据我所知，@ Andriy M的解决方案(如下)的工作原理与Val完全相同.

@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 

编辑#2

MS Access中的表格:

Table from MS Access:

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

使用@Andriy M的解决方案，您可以获得完全相同的结果.

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

此后，我发现@GMastros有更好的解决方案

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

请注意，我更改了搜索模式以包含负号 和小数点分隔符.我也将演员的数据类型更改为 浮动. Val返回vb6 double，这与SQL Server相同 漂浮. -G. Mastros

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

这篇关于我们在SQL SERVER中是否具有与MS Access的VAL()函数等效的功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！