Using SQL Server (T-SQL) to parse text

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • robboll

    Using SQL Server (T-SQL) to parse text

    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

    #2
    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
    Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

    Comment

    • dbmonitor

      #3
      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
      DBMonitor assists DBA's in monitoring the performance of Microsoft SQL Servers and the processes that are running on them.


      Comment

      Working...