2

I have a string like 

ADN120_XK7760069988881LJ

in one of my columns. I have to extract the number with 13 digits length. For example, in the above case, I want to extract 7760069988881 in SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mightyknows
  • 59
  • 1
  • 6
  • 2
    Is is always starting in the exact same location in your string or can it fall anywhere in there? – Sean Lange Oct 04 '17 at 20:45
  • Hi Sean , It can start from anywhere, not the same location , but i have to extract 13 digit code every time regardless of the location. – mightyknows Oct 05 '17 at 09:48

2 Answers2

7

using patindex() with substring() (using a variable for the pattern and replicate() to simplify repeating [0-9] 13 times):

create table t (val varchar(128));
insert into t values ('ADN120_XK7760069988881LJ');

declare @pattern varchar(128) = '%'+replicate('[0-9]',13)+'%';

select substring(val,patindex(@pattern,val),13)
from t;

rextester demo: http://rextester.com/MOEVG64754

returns 7760069988881

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • While this does work on the given sample data, it fails if the string is like `ADN120_XK17760069988881LJ7760069988881`. It returns the first 13 characters of the 14-digit number, instead of the 13-digit number. Nifty trick though. – Tab Alleman Oct 04 '17 at 20:52
  • @TabAlleman If that is an issue, change `+'%'` to `+ '[^0-9]%'` to the end of the `replicate()` – SqlZim Oct 04 '17 at 21:17
  • That's what I was thinking. But you'd actually have to change the '%' on both sides wouldn't you? Otherwise you get the last 13 digits of the 14-digit number. – Tab Alleman Oct 04 '17 at 21:18
  • @TabAlleman Ya, you're right that isn't going to work. If 14 digit numbers are an issue, need more info in the question. – SqlZim Oct 04 '17 at 21:22
0

Creating TEMP table with your query

SELECT 'ADN120_XK7760069988881LJ' CODE INTO #TEMP

Solution using regular expression

SELECT SUBSTRING(CODE,PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',CODE),13)
FROM #TEMP

Couldn't reduce the number of times [0-9] used

Hope this helps

Pawel Czapski
  • 1,856
  • 2
  • 16
  • 26
Jaffar Sadiq
  • 1