parsing string into sequence of numbers and letters?

[VMI]

How can I parse a string and determine that it has the pattern "AA99BB"
(group of letters, group of numbers, group of letters) or "AA99" (letters
and numbers) or "99BB" (numbers and letters)? I created two functions that
extract the numbers and the letters of a string, but I'm not sure how to
accomplish tthis with these functions.
My target would be to parse these strings so that it looked like this in my
table:
"AA99BB" would be divided into - Col1: "AA", Col2: "99", Col3: "BB"
"AA99" - Col1: "AA", Col2: "99", col3: null
"99BB" - Col1: null, Col2: "99", col3: "BB"

Any help is appreciated.
Thanks.

[Bob]

If the strings are always like the ones you've listed then why not keep it
simple with something like this?:

DROP TABLE #tmp
GO
CREATE TABLE #tmp ( raw_data VARCHAR(6) )
GO

SET NOCOUNT ON

INSERT INTO #tmp VALUES( 'AA99BB' )
INSERT INTO #tmp VALUES( 'AA99' )
INSERT INTO #tmp VALUES( '99BB' )

SET NOCOUNT OFF

-- INSERT INTO ......
SELECT SUBSTRING( raw_data, 1, 2 ), SUBSTRING( raw_data, 3, 2 ), SUBSTRING(
raw_data, 5, 2 ),*
FROM #tmp
WHERE raw_data Like '[a-z]%'
UNION ALL
SELECT '', SUBSTRING( raw_data, 1, 2 ), SUBSTRING( raw_data, 3, 2 ),*
FROM #tmp
WHERE raw_data Like '[0-9]%'

Let me know how you get on.

wBob

[VMI]

Thanks for the post.
They could be different sizes ("AA99BB" or "AAA99BBB", etc...). So I really
can't use your script because it retrieves a specific size.

"Bob" <B...@discussions.microsoft.com> wrote in message
news:AC3F90E3-079F-4289...@microsoft.com...

[Razvan Socol]

Hello, VMI

See my response in this thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c96880c4ad5ef3c2

Razvan