Using SQL Server (T-SQL) to parse text

[robboll]

In MS Access 2000 if I have a String such as:

Column1
Delta CC: 123
Charley CC: 234
Foxtrot CC: 890

and I wanted to extact just the numbers in to a field called CC

I could use this formula in a calculated field:

CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)

resulting in:

CC
123
234
890


Any idea on what the code should be within a view in SQL Server?

also -- what is a good reference that can help with these types of
problems.

Any help appreciated!

RBollinger

[Erland Sommarskog]

Re: Using SQL Server (T-SQL) to parse text

[posted and mailed, please reply in news]

robboll (robboll@hotmai l.com) writes:[color=blue]
> In MS Access 2000 if I have a String such as:
>
> Column1
> Delta CC: 123
> Charley CC: 234
> Foxtrot CC: 890
>
> and I wanted to extact just the numbers in to a field called CC
>
> I could use this formula in a calculated field:
>
> CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)
>
> resulting in:
>
> CC
> 123
> 234
> 890
>
>
> Any idea on what the code should be within a view in SQL Server?
>
> also -- what is a good reference that can help with these types of
> problems.[/color]

Look up Books Online, Transact-SQL Reference, String Functions.

For the example at hand, you could try:

substring(Colum n1, charindex('CC:' , Column1) + 3, len(Column1))

But I have to look up how charindex works about everytime I use it, so
you better double-check me.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[dbmonitor]

Re: Using SQL Server (T-SQL) to parse text


robboll wrote:[color=blue]
> In MS Access 2000 if I have a String such as:
>
> Column1
> Delta CC: 123
> Charley CC: 234
> Foxtrot CC: 890
>
> and I wanted to extact just the numbers in to a field called CC
>
> I could use this formula in a calculated field:
>
> CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)
>
> resulting in:
>
> CC
> 123
> 234
> 890
>
>
> Any idea on what the code should be within a view in SQL Server?
>
> also -- what is a good reference that can help with these types of
> problems.
>
> Any help appreciated!
>
> RBollinger[/color]

If all columns are this syntax, you could use the patindex function.

select cast(substring( <col>, patindex('%[0-9]%', <col>), 999) as int)
from <table>

This will convert all strings you have supplied above to numbers.

If there could be numbers before the actual ones you want to strip, use
the reverse function:

select cast(reverse(le ft(reverse(<col >), patindex('%[0-9] %',
reverse(<col>)) )) as int)
from <table>

--
David Rowland
For a good user and performance monitor, check DBMonitor

http://dbmonitor.tripod.com