none
how to get first word value from special character string ?

    Question

  • Hi,

    How to get first character from special characters string.

    for ex. i have below string and want to get first word 'Yes' -
    ~~~~~~~Yes~~~~~~~~No~No~~~~~~~~~

    and also another caseis , where i want 'Maybe' -
    Maybe~~~~~~~~No~No~~~~~~~~~

    Is there any approach ? Thanks

    Friday, November 15, 2013 5:37 AM

Answers

  • Try,

    declare @test table(c1  varchar(100))
    insert into @test values ('~~~~~~~Yes~~~~~~~~No~No~~~~~~~~~'),('Maybe~~~~~~~~No~No~~~~~~~~~')
    ;with cte
    as
    (
    select c1,PATINDEX('%[a-z]%',c1 ) [char_pos]
    ,STUFF(c1,1,PATINDEX('%[a-z]%',c1 )-1,'') [temp_c1]
     from @test
    )
    select STUFF(temp_c1,PATINDEX('%[^a-z]%',temp_c1),LEN(temp_c1),''),c1 from cte
    


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

    • Proposed as answer by Latheesh NKMVP Friday, November 15, 2013 6:07 AM
    • Marked as answer by Maggy111 Monday, November 18, 2013 5:01 AM
    Friday, November 15, 2013 5:51 AM
  • Maggy111,

    You can have two approaches with same code (pasted below). Either you can have a computed columns as shown in the code or you can update that column later.

    Check this.

    DECLARE @Table TABLE (String NVARCHAR(100), FirstValidWord AS SUBSTRING(String,PATINDEX('%[a-z]%',String),PATINDEX('%[^a-z]%',RIGHT(String,LEN(String)-PATINDEX('%[a-z]%',String)))))
    
    INSERT @Table(String)
    SELECT 'Maybe~~~~~~~~No~No~~~~~~~~~'
    UNION
    SELECT '~~~~~~~Yes~~~~~~~~No~No~~~~~~~~~'
    
    SELECT * FROM @Table
    


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

    • Marked as answer by Maggy111 Monday, November 18, 2013 5:00 AM
    Friday, November 15, 2013 7:10 AM

All replies

  • Try,

    declare @test table(c1  varchar(100))
    insert into @test values ('~~~~~~~Yes~~~~~~~~No~No~~~~~~~~~'),('Maybe~~~~~~~~No~No~~~~~~~~~')
    ;with cte
    as
    (
    select c1,PATINDEX('%[a-z]%',c1 ) [char_pos]
    ,STUFF(c1,1,PATINDEX('%[a-z]%',c1 )-1,'') [temp_c1]
     from @test
    )
    select STUFF(temp_c1,PATINDEX('%[^a-z]%',temp_c1),LEN(temp_c1),''),c1 from cte
    


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

    • Proposed as answer by Latheesh NKMVP Friday, November 15, 2013 6:07 AM
    • Marked as answer by Maggy111 Monday, November 18, 2013 5:01 AM
    Friday, November 15, 2013 5:51 AM
  • Maggy111,

    You can have two approaches with same code (pasted below). Either you can have a computed columns as shown in the code or you can update that column later.

    Check this.

    DECLARE @Table TABLE (String NVARCHAR(100), FirstValidWord AS SUBSTRING(String,PATINDEX('%[a-z]%',String),PATINDEX('%[^a-z]%',RIGHT(String,LEN(String)-PATINDEX('%[a-z]%',String)))))
    
    INSERT @Table(String)
    SELECT 'Maybe~~~~~~~~No~No~~~~~~~~~'
    UNION
    SELECT '~~~~~~~Yes~~~~~~~~No~No~~~~~~~~~'
    
    SELECT * FROM @Table
    


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

    • Marked as answer by Maggy111 Monday, November 18, 2013 5:00 AM
    Friday, November 15, 2013 7:10 AM