时间戳

首页 > 解决方案 > SQL Server pull out only data after = OR only the numerics

sql - SQL Server pull out only data after = OR only the numerics

问题描述

It seems that a regular expression would be ideal, yet some team members are not fond of regex...

Problem: Data in a column (from a mainframe flat file import) looks like 2 different ways 

BreakID = 83823737237
OR
MFR BreakID=482883

Thus, the differences are a space before numerics, length of both the alphacharacter before the equals varies and finally the length of the numbers will vary.

Seems I have a few approaches,
1. Everything after the = sign , and trim ? 2. regex , get only the numerics?

So I found this code, in which I assume PATINDEX is standard way of doing regex in -tsql ? what is "string" in this?

SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
                string) + 1) AS Number

How would this be solved with best practices?

标签： sqlsql-serverregextsql

解决方案

答案与 scsimon 略有不同。当我必须抓住字符串末尾的 vals 时，我通常会走这条路。您反转字符串并抓取键值的第一个实例的位置（在本例中为“=”）。使用 charindex 获取该位置，然后使用该 charindex 值获取 RIGHT() 字符。

DECLARE @val1 VARCHAR(100) = 'BreakID = 83823737237'
DECLARE @val2 VARCHAR(100) = 'MFR BreakID=482883'

SELECT 
LTRIM(RTRIM(RIGHT(@val1, CHARINDEX('=', REVERSE(@val1), 0)-1)))
,LTRIM(RTRIM(RIGHT(@val2, CHARINDEX('=', REVERSE(@val2), 0)-1)))

如果你有奇怪的情况，这个解决方案会很好，比如如果你有一家公司SQL=Cool在你的数据中调用它并且它需要一个 ID：

'SQL=CoolID = 12345'

你还想得到 12345。

推荐阅读