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

select only numeric values from varchar column in sql
how to extract numeric values from a string in sql server
sql server return numbers only
patindex in sql server
sql find last number in string
sql server substring
sql server only digits
sql server pattern matching

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?

Seems like a good use case for substring and replace with charindex

We take the substring from everything starting with the first value after the = up to 99 digits (or how ever many you want to enter). We use replace to get rid of the leading space, if there is one.

select replace(substring(stringColumn,charindex('=',stringColumn) + 1,99),' ','')

sql sql-server sql-server-2008 tsql. Say i have a few fields like Extract only numbers (without using while loop) and check each and every character to see if it is a number and extract it. Declare @s varchar(100) if you have data like above in the image, then use the below query select field_3 from table  For the rest of the folks reading this, here's how easy it is to generate huge amounts of various types of random data in SQL Server without a CLR or even building a UDF in SQL Server using a "pseudo cursor" as a million row row-source and some very, very simple math.

Slightly different answer than scsimon. I usually go this route when I have to grab the vals at the end of a string. You reverse the string and grab position of the first instance of your key value ('=' in this case). Get that position with charindex, and then grab the RIGHT() chars using that charindex value.

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

This solution will play nice if you have weird cases, like if you have a company called SQL=Cool in your data and it needs an ID:

'SQL=CoolID = 12345'

and you wanted to still get 12345.

by George Mastros (gmmastros) on December 12, 2008 in category Data Modelling and Design show you how to extract a number from a varchar column that contains letters and numbers. First, let's take a look at what some of that data might look like: Suppose we wanted to extract just the numeric portion of the text. How do you get a numeric-only string and ignore all non-numeric characters, using just standard SQL functions? There’s SQL replace and pattern functions, but they only work once on a string. So barring 53 nested REPLACE functions, it’s tough. I’ve run into this issue many times before while writing import scripts and the like.

That solution is good and versatile, although it sounds like your string will always have an = so you could write something more specific around that if you want to.

That solution finds the start location of the first number string:

PATINDEX('%[0-9]%', string)

And finds the location of the first non-numeric character after that number string (adding a 't' to the end of the string, in case it ends in a number which would otherwise throw an error):

PATINDEX('%[0-9][^0-9]%', string + 't')

And finally it subtracts the start position of the number from the end position to find the length of the number string, and pulls that length out with substring:

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

Here "string" is a placeholder that should be replaced with your column name. Also, the easiest way to test stuff like this in tsql is to use a variable:

DECLARE @string varchar(100) = 'foo bar la la la 83823737237'

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

Output:

83823737237

SELECT only numeric part of string RRS feed · SQL Server. > Transact-SQL. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more SQL Server pull out only data after = OR only the numerics

Kaizen: go for the simple solution, not the perfect one

SELECT substring(c, charindex('=', c), 999)

I'm assuming the column you're putting this in is some kind of number. Sqlserver doesn't care about leading spaces when casting to a number

If it's going in a string column then wrap it in a ltrim()

Now to your questions

1 .. trim

Sure, as above

2 regex...

Not implemented in sqlserver unless you use CLR

PATINDEX ...

It's like regex but it's a very limited subset that only does searching, only returns one string index, doesn't capture, has limited/no character classes. It's more like dos/vb6 wildcards/like than regex

...best practice?

Look at it simply; you're getting the part of a string after an =, not landing on the moon. the best solution to minor optimisations like these is the one that requires the least amount of mental effort from the next human who takes over your job, to get up to speed with this (it'll still be being used in 20 years) :)

I have earlier wrote article about SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only and it  Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 12 SQL Server database books, 33 Pluralsight courses and has written over 5100 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 17+ years of hands-on experience, he holds a Masters of Science degree

numeric This is a synonym for decimal—they're one and the same. money This monetary data values from —263 (—922,337,203,685,477.5808) through 263 — 1 pi will be stored as 3.14, with only two numbers after the decimal point. real but you're storing a name with only 10 characters, the field will take up only 10  SQL Server 2014 Development Essentials (ISBN: 978-1782172550) is an easy-to-follow yet comprehensive guide that is full of hands-on examples. This book will provide you with all the skills you need to successfully design, build, and deploy databases using SQL Server 2014.

Minimum returns the minimum of values from the column. The Sum, Average, Minimum, and Maximum operations can be performed on only numeric data. If you're pulling data from a relational database, consider putting an ORDER BY  The ISNUMERIC () function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0. ISNUMERIC ( expression) Parameter Values. Required. The value to test. Technical Details. SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.

I try to extract number from a string, is there a SQL Server function to do that? Forum Etiquette: How to post data/code on a forum to get the best help[/url] first blank character ' '? Or all numeric characters prior to the first non-numeric? a few through a few passes to get it down to just the house number. Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Comments
  • SQL Server hasn't yet implemented regular expressions.
  • Surely ltrim would be more efficient than replace?
  • Getting an error though The conversion of the varchar value '82332617284' overflowed an int column.
  • Probably @CaiusJard
  • @ChadJacobs well, youre trying to stuff a number (82.33 billion) that's bigger than the max value for an INT (2.15 billion) into an INT column. Either cut your number down or make your column bigger. It's not a failing of the solution, it's choosing an inappropriate data type for the incoming data
  • @scsimon also, the replace is unnecessary now we know he has a numeric column