Pulling out the first occurence of a four-digit number from a field

[Rydel]

Sorry if this is a very basic simple question for SQL gurus. So here's the problem:

I have a large text field in a table "people" (field named "CV") and I want to pull out the first four digit number I find in this field and store it in another field called "year_born". The problem is that "CV" field has differen format for different people:

"some text (1970-xxxx)..."
"other text... born 1951"
"(1920-2004)..."

But in 99% of the cases the first four-digit number <i>is</i> the year of birth. Is there a quick way of pulling it out from the "CV" field using SQL?

Big thanks in advance!



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[Rydel]

Sorry, but actually the table is currently in MS Access, so if possible could you please give Jet SQL syntax, not Transact-SQL (in case there would be differences).



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[donutman]

Post in the Access forum.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Rydel]

Alright. Sorry. Should I delete it?