will i be able to extract the year stamp from these strings?

Question

will i be able to extract the year stamp from these strings?

polkadot

SSChampion

Points: 13664
More actions
December 31, 2013 at 2:07 pm

#287771

Hi folks, I need to know if it's possible, with T-SQL, to extract the year stamp from strings as variable as the following:
iMac (21.5-inch, Late 2013)
iMac (27-inch, Late 2013)
20-inch/17-inch iMac (Early 2006)
17-inch iMac (Mid 2006), 1.83 GHz Intel Core Duo
iMac (20-inch + 17-inch, Late 2006)
iMac (17-inch Late 2006 CD)
The year can appear anywhere in the string and is not the only string containing numbers.....
--Quote me

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Not really extracting, but as long as the year numbers can't appear in any other part of the string and the number of years is finite...

create table #stuff (

badstrings varchar(255) null)

insert #stuff

select 'iMac (21.5-inch, Late 2013)'

union

select 'iMac (27-inch, Late 2013)'

union

select '20-inch/17-inch iMac (Early 2006)'

union

select '17-inch iMac (Mid 2006), 1.83 GHz Intel Core Duo'

union

select 'iMac (20-inch + 17-inch, Late 2006)'

union

select 'iMac (17-inch Late 2006 CD)'

select *, case when badstrings like '%1999%' then '1999'

when badstrings like '%2000%' then '2000'

when badstrings like '%2001%' then '2001'

when badstrings like '%2002%' then '2002'

when badstrings like '%2003%' then '2003'

when badstrings like '%2004%' then '2004'

when badstrings like '%2005%' then '2005'

when badstrings like '%2006%' then '2006'

when badstrings like '%2007%' then '2007'

when badstrings like '%2008%' then '2008'

when badstrings like '%2009%' then '2009'

when badstrings like '%2010%' then '2010'

when badstrings like '%2011%' then '2011'

when badstrings like '%2012%' then '2012'

when badstrings like '%2013%' then '2013'

else 'not found'

end

from #stuff

When in doubt, reframe the problem...

And then again, I might be wrong ...
David Webb

polkadot SSChampion Points: 13664 More actions · Answer 2

I didn't think about the simple approach. I immediately conjured the problem to involve complex nesting of substring and pathindex functions. Thanks David and Happy New Year!

--Quote me

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

That's where I went too and realized I couldn't solve that. Happy New Year to you too!

And then again, I might be wrong ...
David Webb

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

This seems to work fine, unless you have other 4 digits number in your string.

If you have any questions on how it works or why did I used something, feel free to ask.

--Using David's sample data

SELECT badstrings, CASE WHEN PATINDEX( '%[1-2][0-9][0-9][0-9][^0-9]%',badstrings+'.') > 0

THEN SUBSTRING( badstrings, PATINDEX( '%[1-2][0-9][0-9][0-9][^0-9]%',badstrings+'.'), 4)

ELSE '' END

FROM #stuff

Happy New Year 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

polkadot (12/31/2013)
Hi folks, I need to know if it's possible, with T-SQL, to extract the year stamp from strings as variable as the following:
iMac (21.5-inch, Late 2013)
iMac (27-inch, Late 2013)
20-inch/17-inch iMac (Early 2006)
17-inch iMac (Mid 2006), 1.83 GHz Intel Core Duo
iMac (20-inch + 17-inch, Late 2006)
iMac (17-inch Late 2006 CD)
The year can appear anywhere in the string and is not the only string containing numbers.....

For better accuracy, lemme ask... will the year ALWAYS be preceded by the word Early, Mid, or Late?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)