how to test for and pull out a string

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to test for and pull out a string

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-02-07 : 02:24:16

Hi
I have a field in my database that has values typical to the following "417 INHS Birm 101109 SEE NOTES" (without the quotes)

It will ALWAYS have a 6 character date represenatation in it (e.g. 101109 for the above example represents 10th Nov 2009 (uk date), although the 6 characters could be anywhere in the string.
How can I find them and pull them out?

Thanks a lot

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:53:20

[code]
DECLARE @TEMP TABLE
(
TestString varchar(100)
)

INSERT INTO @TEMP
SELECT '417 INHS Birm 101109 SEE NOTES' UNION ALL
SELECT '101109 SEE NOTES' UNION ALL
SELECT '417 INHS Birm 101109'

SELECT SUBSTRING(TestString, PATINDEX('% [0-3][0-9][0-1][0-9][901][0-9] %', ' ' + TestString + ' '), 6),
TestString
FROM @TEMP
[/code]
I have assumed that the format is ddmmyy and dd=00 - 39, mm=00-19 and yy=90-19 - you can adjust the Patterns to broaden that if you need to. I've gone for a "tight-ish" pattern to reduce mismatches.

Also assumed that the 6-digits will be surrounded by spaces if it is within the field - that will help prevent mismatches with FOO123456BAR for example

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-02-07 : 03:13:19

Thanks a lot
will give it a go -

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-02-07 : 12:08:34

worked brilliantly thanks a bunch:O)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources