Find number in string

[spiff2002]

is there a function to find numbers in a string? Varchar type or any kind of stirng type?
We have a large item master table for wines. In the original design, the vintage was included in the description. I am trying to create a second column with the vintage. I already know that this won't work 100% because they might be items that have numbers as part of the description but a functions like this woud help me cover close to 85 to 90% of the items.

Example:
Description "Folonari 05 Pinot Grigio" I would like to split it into to columns
Descr Vintage
"Folonari Pinot Grigio" 05

I will deal with the four digit format later.

Thank you in advance for any ideas

Frank

 

[gmmastros]

I take it this is a 1 time query. Does the year indicator appear in every description? Will it always be 2 digits?

Try this query. I'm not guaranteeing that it will work for all of your data, but you could try something like...

[COLOR=blue]select[/color] [COLOR=#FF00FF]LTrim[/color]([COLOR=#FF00FF]RTrim[/color]([COLOR=#FF00FF]SubString[/color]([COLOR=red]' '[/color] + [!]FieldName[/!] + ' ', patindex('% [0-9][0-9] %', ' ' + [!]FieldName[/!] + ' '), 4)))
[COLOR=blue]From[/color]   [!]TableName[/!]
[COLOR=blue]Where[/color]  [COLOR=#FF00FF]IsNumeric[/color]([COLOR=#FF00FF]SubString[/color]([COLOR=red]' '[/color] + [!]FieldName[/!] + ' ', patindex('% [0-9][0-9] %', ' ' + [!]FieldName[/!] + ' '), 4)) = 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[spiff2002]

It worked out perfect. Yes it is usually the two digit format for the vintage.
Thanks a lot
Frank