MySQL Query用于获取字符串值中的Integer [英] MySQL Query for getting an Integer in a string value

问题描述

大家好..



我在使用MySQL查询从字符串中获取整数值时遇到问题。



假设我有StoreId，它有ST-1到ST-n值，我只想得到给定字符串的整数或数字。



我怎么想用MySQL查询得到整数？



谢谢！

解决方案

< blockquote>对于MYSQL检查此链接希望这将帮助您：



http://grokbase.com/t/mysql/mysql/054ph638hw/extract-numeric-value-from-a-string [ ^ ]



http://stackoverflow.com/questions/978147/how-do-y ou-extract-a-numerical-value-from-a-string-in-a-mysql-query [ ^ ]



在SQL Server中我们可以这样做：

 声明  @ var   nvarchar （max）=  '  ST-1' 
  SELECT   LEFT （Val，PATINDEX（' ％[^ 0-9]％' ，Val + '  a'） -  1）来自（
  SELECT  SUBSTRING（ @ var ，PATINDEX（' ％[0- 9]％'， @ var ），LEN（ @ var ））Val 
）x 

尝试类似

 < span class =code-keyword> SELECT   right （rtrim（StoreId），
 LEN（rtrim（StoreId）） -   3 ）
  FROM  TableName 

我想这可能会对你有所帮助。



http://stackoverflow.com/questions/5734504/mysql-left-part-of-a-string-split-by-a-separator-string [ ^ ]

Hi to all..

I have a problem in getting an integer value from a string using MySQL query.

Suppose, i have StoreId which has "ST-1 to ST-n" value and i want only to get the integer or number of the given string.

How am I suppose to get the integer using MySQL query?

Thank you!

解决方案

For MYSQL Check this link hope this wil help you :

http://grokbase.com/t/mysql/mysql/054ph638hw/extract-numeric-value-from-a-string[^]

http://stackoverflow.com/questions/978147/how-do-you-extract-a-numerical-value-from-a-string-in-a-mysql-query[^]

In SQL Server we can do like this :

declare @var nvarchar(max)='ST-1'
SELECT LEFT(Val,PATINDEX('%[^0-9]%', Val+'a')-1) from(
    SELECT SUBSTRING(@var, PATINDEX('%[0-9]%', @var), LEN(@var)) Val
)x

Try something like

SELECT right(rtrim(StoreId),
             LEN(rtrim(StoreId)) - 3)
FROM TableName

I guess this may help you.

http://stackoverflow.com/questions/5734504/mysql-left-part-of-a-string-split-by-a-separator-string[^]

这篇关于MySQL Query用于获取字符串值中的Integer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！