none
How to select any string value between double qoutes in T-SQL RRS feed

  • Question

  • How To select string value between double quotes?

    I will have random text entered from users and I need to pick the text string in the double codes and place it in a new table.

    Ex: SourceTable

    ___________________

    Description                 

    I want to order "Mango"

    My fav color is "Blue"

    I want to go to "Paris"

    ___________________

    The Query must read SourceTable and Fil the Destination Table like below.

    ________

    WORDS    

    Mango

    Blue

    Paris

    ________

    Wednesday, September 11, 2019 3:53 AM

All replies

  • Hi prathima prasad,

    Hope the below code will help you!

    DECLARE @TAB AS TABLE(INPUT VARCHAR(500));
    INSERT @TAB VALUES 
    ('I WANT "MANGO"'),
    ('My fav color is "Blue"'),
    ('I want to go to "Paris"'),
    (''), --> SAMPLE DATA ADDED
    (NULL);  --> SAMPLE DATA ADDED
    
    
    SELECT 	INPUT,
    SUBSTRING(
       (SUBSTRING(
     			INPUT,
    			CHARINDEX ('"', INPUT)+1,
    			LEN(INPUT) 
    		  )
    	),
    	0,
    	CHARINDEX
    	('"',(SUBSTRING(
    		INPUT,
    		CHARINDEX ('"', INPUT)+1,
    		LEN(INPUT)
    	)),0)
    	) AS EXPECTED_RESULTSET
    FROM @TAB;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Wednesday, September 11, 2019 4:16 AM
  • Hi Prathima Prasad,

    Please try the following:

    -- DDL and sample data population, start
    DECLARE @tbl AS TABLE(ID INT IDENTITY(1,1) PRIMARY KEY, INPUT VARCHAR(500) NOT NULL);
    INSERT INTO @tbl VALUES 
    	('I want to order "Mango"'),
    	('My fav color is "Blue"'),
    	('I want to go to "Paris"'),
    	('');
    -- DDL and sample data population, end
    
    SELECT * 
    	, PARSENAME(REPLACE(INPUT, '"', '.') + ' ', 2) AS result
    FROM @tbl;
    

    Output:

    ID	INPUT	result
    1	I want to order "Mango"	Mango
    2	My fav color is "Blue"	Blue
    3	I want to go to "Paris"	Paris
    4		NULL


    Wednesday, September 11, 2019 4:32 AM
  • Hi Prathima,

    Please try this : 

    create table test ( [Description]  varchar(50))
    go
    insert into test values
    ('I want to order "Mango"'),
    ('My fav color is "Blue"'),
    ('I want to go to "Paris"')
    
    
    select *,SUBSTRING([Description],
    CHARINDEX('"',[Description],1)+1,
    CHARINDEX('"',[Description],CHARINDEX('"',[Description],1)+1)-CHARINDEX('"',[Description],1)-1)
    from test 
    /*
    Description               
    ------------------------- ----------
    I want to order "Mango"   Mango
    My fav color is "Blue"    Blue
    I want to go to "Paris"   Paris
    */

    Sabrina 


    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.

    Wednesday, September 11, 2019 5:46 AM
  • create table #temp (col1 varchar(200))
    insert #temp values ('I want to order "Mango"'),('My fav color is "Blue"'),('I want to go to "Paris"')

    select *,replace(right(col1,len(col1)-charindex('"',col1)),'"','')
    from #temp
    Wednesday, September 11, 2019 9:40 AM
  • Hi prathima

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers or share your methods with us. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    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.

    Tuesday, September 17, 2019 6:40 AM
  • Create table SourceTable(Description VARCHAR(50));
    INSERT SourceTable VALUES 
    ('I WANT "MANGO"'),
    ('My fav color is "Blue" '),
    ('I want to go to "Paris"');
     
    
    
    select * 
    ,Stuff(
    stuff(Description,1,charindex('"',Description),'')
    ,charindex('"',stuff(Description,1,charindex('"',Description),''))
    , len(Description)
    ,'')  as WORDS
                     
    from SourceTable
    
    
    drop table SourceTable
    
    

    Wednesday, September 18, 2019 2:10 PM
    Moderator