How to get values enclosed within special characters in a long string

Question

I have a long string containing three special symbols ' @ and ? and some text are enclosed inside these symbols.

for ex. "@sa@32@ddd@?222?@ds@asa@hhh@?ddsds?dsdsd?cccc?'cxcx'?ccxc?cxc?'cxcx'"

I want the values sa 32 ddd 222 ds etc.. and insert these values in a table. Next step is to insert these values inside different column of same row in a table.

How can I achieve that.

You need to "split" the string. See http://stackoverflow.com/questions/2507330/sql-server-split-operation — , Feb 23 '16 at 11:22
See [Query to get only numbers from a string](http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) — Khan Abdulrehman, Feb 23 '16 at 11:39

score 2 · Accepted Answer · answered Feb 23 '16 at 11:55

The following function allows you to split the given string on the basis of a single delimiter.

CREATE FUNCTION [dbo].[fnSplitString] 
( 
  @string NVARCHAR(MAX), 
  @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
  DECLARE @start INT, @end INT 
  SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
  WHILE @start < LEN(@string) + 1 BEGIN 
    IF @end = 0  
        SET @end = LEN(@string) + 1

    INSERT INTO @output (splitdata)  
    VALUES(SUBSTRING(@string, @start, @end - @start)) 
    SET @start = @end + 1 
    SET @end = CHARINDEX(@delimiter, @string, @start)

  END 
  RETURN 
END

You can use this function iteratively for each of the individual delimiters, over each individual string obtained from the first run and the final output should provide you the requisite values.

It also contains the values not enclosed in special symbols. — Sanjeev Singh, Feb 24 '16 at 09:21
@SanjeevSingh it would still work. If there are values that are not enclosed in special symbols, it would return them as is. — Shubham Pandey, Feb 25 '16 at 12:05

score 1 · Answer 2 · answered Feb 23 '16 at 11:52

1

Try this way. The complete answer It depends od more information

SET QUOTED_IDENTIFIER OFF

declare @test varchar(100)

set @test ="@sa@32@ddd@?222?@ds@asa@hhh@?ddsds?dsdsd?cccc?'cxcx'?ccxc?cxc?'cxcx'"

print replace(replace(@test,'@',' '),'?',' ')

answered Feb 23 '16 at 11:52

Krismorte

642
7
24

It does not insure a value not enclosed will be filtered. – Sanjeev Singh Feb 24 '16 at 09:17

How to get values enclosed within special characters in a long string

2 Answers2