Remove certain characters from a string

• ocean3300

  SSChasing Mays

  Points: 621

  More actions

  January 5, 2017 at 3:07 pm

  #401320

  DECLARE @Text VARCHAR(256)

  SET @Text = 'Generic="abcde" Generic="fghij' Generic="sdfsdfasdf"'

  How can I write a query so that the result can be displayed as below?

  - Find Generic="" from a string (there could be multiple as shown in @Text variable)

  - Remove any characters inside ""

  'Generic="" Generic="" Generic=""'

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  January 5, 2017 at 3:53 pm

  #1921004

  I'm about to leave on vacation, so I don't have time to do a thorough write-up, but the general approach is to use a tally table to get a row for every single character in the string then use the windowed function to count the number of double quotes from the beginning of the string to the current character and ignore any characters where that count is odd (x%2 = 1) and the character is not a double quote.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 5, 2017 at 11:05 pm

  #1921032

  Here is a quick solution that uses the dbo.DelimitedSplit8K function[/url]. Note that for this to work, the values cannot contain spaces!

  😎

  DECLARE @Text VARCHAR(256) = 'Generic="abcde" Generic="fghij" Generic="sdfsdfasdf"';

  SELECT

  STUFF((

  SELECT

  CHAR(32) + LEFT(X32.Item,CHARINDEX(CHAR(34),X32.Item,1)) + CHAR(34)

  FROM dbo.DelimitedSplit8K(@Text,CHAR(32)) X32

  ORDER BY X32.ItemNumber ASC

  FOR XML PATH(''),TYPE

  ).value('(./text())[1]','VARCHAR(256)'),1,1,'') AS STR_VALUES_REMOVED

  ;

  Output

  STR_VALUES_REMOVED

  ---------------------------------

  Generic="" Generic="" Generic=""

• ocean3300

  SSChasing Mays

  Points: 621

  More actions

  January 6, 2017 at 9:37 am

  #1921154

  Thanks for the ideas and codes.

  I have another question.

  Let's say I have a string something like below:

  test-asdasdasd="xxx"

  If I want to replace asdasdasd="xxx" with blank ('') so that I can only return test, I can run below.

  SELECT REPLACE(test-asdasdasd="xxx", 'asdasdasd="asdasd"','')

  What if the characters inside of double quotes (xxx) are always different, how can I write a query to replace it?

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 6, 2017 at 10:05 am

  #1921161

  ocean3300 (1/6/2017)

  ---

  Thanks for the ideas and codes.

  I have another question.

  Let's say I have a string something like below:

  test-asdasdasd="xxx"

  If I want to replace asdasdasd="xxx" with blank ('') so that I can only return test, I can run below.

  SELECT REPLACE(test-asdasdasd="xxx", 'asdasdasd="asdasd"','')

  What if the characters inside of double quotes (xxx) are always different, how can I write a query to replace it?

  You will have to define the logic in a totally unambiguous way and then it almost certainly can be done, think of variations in the patterns, values etc.

  😎

  You can have a look at this article[/url] where a problems of similar and higher complexity are solved.

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  January 8, 2017 at 6:40 pm

  #1921287

  Eirikur Eiriksson (1/5/2017)

  ---

  Here is a quick solution that uses the dbo.DelimitedSplit8K function[/url]. Note that for this to work, the values cannot contain spaces!

  😎

  DECLARE @Text VARCHAR(256) = 'Generic="abcde" Generic="fghij" Generic="sdfsdfasdf"';

  SELECT

  STUFF((

  SELECT

  CHAR(32) + LEFT(X32.Item,CHARINDEX(CHAR(34),X32.Item,1)) + CHAR(34)

  FROM dbo.DelimitedSplit8K(@Text,CHAR(32)) X32

  ORDER BY X32.ItemNumber ASC

  FOR XML PATH(''),TYPE

  ).value('(./text())[1]','VARCHAR(256)'),1,1,'') AS STR_VALUES_REMOVED

  ;

  Output

  STR_VALUES_REMOVED

  ---------------------------------

  Generic="" Generic="" Generic=""

  Dangerous code.

  It will fail if a value between quotes contains a space character.

  EDIT:

  Sorry, missed the note at the beginning of the post.

  _____________
  Code for TallyGenerator

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  January 19, 2017 at 1:45 pm

  #1923854

  ocean3300 - Friday, January 6, 2017 9:37 AM

  Thanks for the ideas and codes. I have another question.Let's say I have a string something like below:test-asdasdasd="xxx"If I want to replace asdasdasd="xxx" with blank ('') so that I can only return test, I can run below.SELECT REPLACE(test-asdasdasd="xxx", 'asdasdasd="asdasd"','')What if the characters inside of double quotes (xxx) are always different, how can I write a query to replace it?

  Here's an idea that does both:

  
DECLARE @Text VARCHAR(256) = 'Generic="abcde" Generic="fghij" Generic="sdfsdfasdf" test-asdasdasd="xxx"';

  WITH ADJUSTED AS (

      SELECT S.ItemNumber,
        STUFF(S.Item, CHARINDEX('"', S.Item) + 1, LEN(S.Item) - 1 - CHARINDEX('"', S.Item), '') + ' ' AS STRING_VALUE
    FROM dbo.DelimitedSplit8K(REPLACE(REPLACE(@Text, '" ', '":'), '=":', '=" '), ':') AS S
)
SELECT (
    SELECT
        CASE CHARINDEX('-', STRING_VALUE)
            WHEN 0 THEN STRING_VALUE
            ELSE STUFF(STRING_VALUE, CHARINDEX('-',STRING_VALUE), CHARINDEX('=', STRING_VALUE) - CHARINDEX('-', STRING_VALUE), '')
        END
    FROM ADJUSTED AS A
    ORDER BY A.ItemNumber
    FOR XML PATH(''), TYPE
    ).value('(./text())[1]','VARCHAR(256)') AS Item


  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)