Prev: List of Logins
Next: Problem with Parallel Query Execution
From: robboll on 3 Feb 2005 17:21
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

From: Erland Sommarskog on 3 Feb 2005 17:47
[posted and mailed, please reply in news]

robboll (robboll(a)hotmail.com) writes:
> 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.

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

For the example at hand, you could try:

substring(Column1, 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(a)sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
From: dbmonitor on 3 Feb 2005 21:04

robboll wrote:
> 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

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(left(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

 | 
Pages: 1
Prev: List of Logins
Next: Problem with Parallel Query Execution