Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pwvailla
Starting Member
31 Posts |
 Posted - 2010-10-21 : 16:41:16
|
| I am trying to find out how I would parse a field defined as a varchar(25) and determine if it begins with a numeric value.Even more exactly, the field is ATTN (varchar 25). I want to know that positions 1-3 and 4-7 are numeric or integer values (or not).Here is an example of some rows of data:337X742 00800 ML-D2B1 REL ---- want to know 337 and 742 as numbers337X166 00800 ML-D2B1 LIB290T479 01300 ML-D4CF RIODAVID FITTS BLDG:262 ---- want to this is NOT a numeric value270T479 04000 ML-N23 RIOThanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-21 : 16:46:58
|
| SELECT * FROM myTable WHERE ATTN LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9]%' |
 |
|
|
pwvailla
Starting Member
31 Posts |
Posted - 2010-11-17 : 16:17:53
|
| thanks! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-17 : 17:04:34
|
That won't work for 2 reasons:1. ISNUMERIC(SUBSTRING(ATTN,4,3)) should be ISNUMERIC(SUBSTRING(ATTN,5,3)) (the 4th character is a non-digit)2. ISNUMERIC recognizes scientific notation (1E5) as a valid numeric value and will return 1, even though "E" is not a digit.The following demonstrates the difference:WITH myTable(ATTN) AS ( SELECT '337X742 00800 ML-D2B1 REL' UNION ALL SELECT '337X166 00800 ML-D2B1 LIB' UNION ALL SELECT '290T479 01300 ML-D4CF RIO' UNION ALL SELECT 'DAVID FITTS BLDG:262' UNION ALL SELECT '270T479 04000 ML-N23 RIO' UNION ALL SELECT '1E6X3e4 Test by Rob Volk')SELECT ATTN, ISNUMERIC(SUBSTRING(ATTN,1,3)) First3, ISNUMERIC(SUBSTRING(ATTN,5,3)) Next3,CASE WHEN ATTN LIKE '[0-9][0-9][0-9]_[0-9][0-9][0-9]%' THEN 'Yes' ELSE 'No' END OnlyDigitsFROM myTable-- resultsATTN First3 Next3 OnlyDigits------------------------- ----------- ----------- ----------337X742 00800 ML-D2B1 REL 1 1 Yes337X166 00800 ML-D2B1 LIB 1 1 Yes290T479 01300 ML-D4CF RIO 1 1 YesDAVID FITTS BLDG:262 0 0 No270T479 04000 ML-N23 RIO 1 1 Yes1E6X3e4 Test by Rob Volk 1 1 No -- isnumeric=1 but "E" is not a digit |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-17 : 17:55:00
|
| There'd still be a problem with decimal points and dashes/negative signs. Truth is pwvailla is testing for a regular expression, it's not a good application for ISNUMERIC or similar functions. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-18 : 05:35:46
|
quote:
Originally posted by X002548
Since you are going to have to do a scan anywayWHERE ISNUMERIC(SUBSTRING(ATTN,1,3)) = 1AND ISNUMERIC(SUBSTRING(ATTN,4,3)) = 1Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
select isnumeric(','), isnumeric('$'), isnumeric('.'), isnumeric('1e2'), isnumeric('2d1'), isnumeric('1,'), isnumeric(',7'), isnumeric(',7,'), isnumeric(',7.')MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Parsing a character string to find a numeric value