none
Extracting text from a string

    Question

  • Hi,

    I have this format (varchar) for time:

    XX<hour>H<minute>M<second>S

    e.g.

    01:23:01 am - XX1H23M1S

    What is the best way to extract the time from this format?


    cherriesh

    • Moved by Olaf HelperMVP Friday, July 26, 2013 3:49 AM Moved from "SQL Database Engine" to a more related forum for a better response
    Friday, July 26, 2013 3:01 AM

Answers

  • Hi ,

    If you dont get zeros appended then try below code :

    DECLARE @timestring VARCHAR(20) = 'XX1H23M1S'
    SELECT  @timestring = REPLACE(REPLACE(REPLACE(REPLACE(@timestring,'XX',''),'H',':'),'M',':'),'S','') 
    SELECT CASE WHEN CHARINDEX(':',@timestring) = 2 
           THEN  CASE WHEN CHARINDEX(':',REVERSE(@timestring)) = 2
           THEN  '0'+REVERSE(STUFF(REVERSE(@timestring), 2, 0, '0'))
           ELSE @timestring END
           ELSE @timestring END [Time]
    Note : PARSENAME function will work only with SQL Server 2012

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by cherriesh Sunday, July 28, 2013 12:33 AM
    Friday, July 26, 2013 6:24 AM
    Moderator

All replies

  • Hi,

    Try like this ,

    DECLARE @timestring VARCHAR(20) = 'XX1H23M1S'
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(@timestring,'XX',''),'H',':'),'M',':'),'S','') [Time]


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Friday, July 26, 2013 3:22 AM
    Moderator
  • DECLARE @DT AS NVARCHAR(50)
    SET @DT=REPLACE(REPLACE(REPLACE(REPLACE('XX1H23M1S','XX',''),'H','.'),'M','.'),'S','')
    SELECT
    PARSENAME(@DT,3) HR,
    PARSENAME(@DT,2) MI,
    PARSENAME(@DT,1) SE


    Regards, RSingh

    Friday, July 26, 2013 3:25 AM
  • the result from this query will be 1:23:1.

    I want to achieve 1:23:01


    cherriesh

    Friday, July 26, 2013 4:08 AM
  • Try the below:

    DECLARE @DT AS NVARCHAR(50)
    SET @DT=REPLACE(REPLACE(REPLACE(REPLACE('XX1H23M1S','XX',''),'H','.'),'M','.'),'S','')
    SELECT
    Right('00'+PARSENAME(@DT,3),2) +':'+
    Right('00'+PARSENAME(@DT,2),2) +':'+
    Right('00'+PARSENAME(@DT,1),2) 
    --------------------------------------------------------------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 26, 2013 4:18 AM
  • Hi ,

    If you dont get zeros appended then try below code :

    DECLARE @timestring VARCHAR(20) = 'XX1H23M1S'
    SELECT  @timestring = REPLACE(REPLACE(REPLACE(REPLACE(@timestring,'XX',''),'H',':'),'M',':'),'S','') 
    SELECT CASE WHEN CHARINDEX(':',@timestring) = 2 
           THEN  CASE WHEN CHARINDEX(':',REVERSE(@timestring)) = 2
           THEN  '0'+REVERSE(STUFF(REVERSE(@timestring), 2, 0, '0'))
           ELSE @timestring END
           ELSE @timestring END [Time]
    Note : PARSENAME function will work only with SQL Server 2012

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by cherriesh Sunday, July 28, 2013 12:33 AM
    Friday, July 26, 2013 6:24 AM
    Moderator
  • Note : PARSENAME function will work only with SQL Server 2012

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.


    No Sathya, PARSENAME will work for 2005,2008, 2008R2 and even 2012.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 26, 2013 6:36 AM
  • Hi Latheesh,

    Apology for the wrong information . Got confused with PARSE(2012 new feature) and PARSENAME

    Yes , PARSENAME will work with 2005,2008, 2008R2 and even 2012.

    The solution you provided will work :)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Friday, July 26, 2013 6:52 AM
    Moderator
  • Hi Sathya - Does not matter really. Just want to share!!!

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 26, 2013 6:57 AM