How to extract specific data from feild in table

• alex4769

  Grasshopper

  Points: 11

  More actions

  March 4, 2003 at 4:50 pm

  #175324

  I have a basic sql script:

  SELECT [ID], [EVENTID], [TYPE], [EVENT_DATE], [CATEGORY], [USERID], [USERNAME], [COMPUTER], [SOURCE], [EVENT_TIME], [IN_WORK_HOURS], [RECID], [RECNO], [PC_SECURITY_LEVEL], [PC_ALERT_LEVEL], [EXT_TAGS], [NO_EXT_TAGS], [GLOBALS], [SELMID], [FLAGS], [SCANNED_COMPUTER] FROM [SELMLOG4].[dbo].[EVENTS]where [EVENTID] = '627' and [USERNAME] = 'BTSINC\SANCHEZJU'

  which returns all the fields I need the problem is that the field ext_data displays:

  @1@BOBADILLAB~1~@2@ZBTSHOU01~2~@3@S-1-5-21-597501654-519571020-924725345-500~3~@4@SANCHEZJU~4~@5@BTSINC~5~@6@(0x0,0x5DD4C62)~6~@7@-~7~

  I need to just display the Domain name from this field, in the example BOBADILLAB. How can I either trim the data displayed to show only the username(BOBADILLAB)? Any Suggestions?

• mromm

  SSCarpal Tunnel

  Points: 4301

  More actions

  March 4, 2003 at 5:05 pm

  #450282

  Look for functions to manipulate with strings in SQL Server Books Online. There are functions like substring, left, right, replace, stuff, etc.

• jpipes

  SSChampion

  Points: 12230

  More actions

  March 5, 2003 at 10:15 am

  #450322

  Try something like this:

  
  
  SELECT
  
    SUBSTRING([EXT_DATA], CHARINDEX('@1', [EXT_DATA]) + 2, (CHARINDEX('@2', [EXT_DATA]) - CHARINDEX('@1', [EXT_DATA])+2))
  
  FROM [SELMLOG4].[dbo].[EVENTS]
  
  WHERE [EVENTID] = '627' AND [USERNAME] = 'BTSINC\SANCHEZJU' 
  
  

  editing: oops, forgot a parentheses...

  Edited by - jpipes on 03/05/2003 10:17:00 AM