none
extract date from column RRS feed

  • Question

  • my customer is asking me to extract PN8BBAN17TCB57506 install date (2.12.16) from the column

    PN8BBAN17TCB57506 (MEP)   INSTALL 2.12.16  PN8BBAN17TCB57289 (MEP)  PN8BBAN17TCB57287 (MEP)  PN8BBAN17TCB57239 (MEP)  PN8BBAN17TCB57275 (MEP) 



    is there any method to get it?

    Friday, August 25, 2017 4:03 AM

All replies

  • Write an application that queries the data and try to extract the date values using regular expression: https://en.wikipedia.org/wiki/Regular_expression

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 25, 2017 5:24 AM
  • Hi kjleong,

    In your scenario, you can use PatIndex() function to get the position of '2.12.16'. Then use Substring() function to extract the date from a string. Please refer to following query, see if it works for you:

    Declare @string nvarchar(max),@STRING1 VARCHAR(1000),@STRING2 VARCHAR(1000)
    select @string='PN8BBAN17TCB57506 (MEP)   INSTALL 2.12.16  PN8BBAN17TCB57289 (MEP)  PN8BBAN17TCB57287 (MEP)  PN8BBAN17TCB57239 (MEP)  PN8BBAN17TCB57275 (MEP) '
    
    select @STRING1=SUBSTRING(@string,1,PATINDEX('% [0-9.]%',@string)),
    @STRING2=Ltrim(SUBSTRING(Ltrim(right(@string,len(@string)-len(@STRING1))),1,CHARINDEX(' ',Ltrim(right(@string,len(@string)-len(@STRING1))))-1))
    
    select @STRING1,@STRING2 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Xi Jin Friday, August 25, 2017 6:29 AM
    • Proposed as answer by Xi Jin Tuesday, September 12, 2017 9:15 AM
    Friday, August 25, 2017 6:29 AM