Extract a character string from a text field

I need to extract a character string from a text field. The string I'm
looking for will always start with the first four characters of "ABC-" and
then will end with three numbers (0-9) in varying combinations, ex.
"ABC-508". The problem is that the position of the string in the text field
is different in each record and the last three characters of the string will
vary as described above. Any help is greatly appreciated
--
Finn GirlOne method:
SELECT
CASE
WHEN PATINDEX('%ABC-[0-9][0-9][0-9]%', MyTextCol) = 0 THEN
NULL
ELSE
SUBSTRING(MyTextCol, PATINDEX('%ABC-[0-9][0-9][0-9]%',
MyTextCol), 7)
END AS ExtractedValue
FROM dbo.MyTable
You can encapsulate the code in a proc or function for reusability.
Hope this helps.
Dan Guzman
SQL Server MVP
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl|||create table #foo (SomeColumn varchar(50))
insert into #foo values ('test ABC-508')
insert into #foo values ('testing ABC-509')
insert into #foo values ('ABC-123')
insert into #foo values ('FinnGirlABC-524')
insert into #foo values ('abcABC-456')
select * from #foo
--this shows you the charindex
SELECT CHARINDEX('ABC-', SomeColumn) FROM #foo
--this gets the desired information:
SELECT SUBSTRING(SomeColumn, (CHARINDEX('ABC-', SomeColumn)), 7) FROM #foo
drop table #foo
Keith Kratochvil
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl