none
How to remove a character from a string without using single quote characters? RRS feed

  • Question

  • I typically use 

    REPLACE(@MyString, CHAR(32), '')

    To remove space characters from a variable, but I'd like to eliminate those two single quotes in the string_replacement value.  Is there something else I can use that doesn't include single quote characters, or perhaps a different function I should be using?

    Seems like there's always different ways of doing things except in this case.

    Thanks,
    Eric B.

    Tuesday, September 25, 2018 5:20 PM

Answers

All replies

  • you can use 

    set @stringHello = 'helloworld'

    concat(Substring(@stringHello, 0, charindex('w', @stringHello)),  Substring(@stringHello, charindex('w', @stringHello)+1, Len(@stringHello)))

    use charindex to find the character position of the characher you want to remove.

    substring to remove the left & right side of the starting from the removal character and concatenate it.

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

    if this resolve the issue, please mark it as answer.

    Tuesday, September 25, 2018 5:37 PM
  • Hi Eric,

    Just curious... The REPLACE() function needs to know what to replace and what to replace it with. Why did you want to get rid of the two single quotes?

    Tuesday, September 25, 2018 5:38 PM
  • REPLACE(@MyString, CHAR(32), char(31))
    Tuesday, September 25, 2018 5:39 PM
  • DECLARE @s varchar(50) = 'This is my ''cat'' but that is not my ''''dog''''';
    SELECT REPLACE(@s, '''''', '');

    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Tuesday, September 25, 2018 6:20 PM
    Tuesday, September 25, 2018 6:20 PM
  • Hi .theDBguy,

    I am creating queries to store in a table that can then be looped through and executed.  These are validation queries to be executed against tables, they're to be written by QA people (not SQL people), and the single quotes cause problems on insert for the QA people.

    Tuesday, September 25, 2018 6:30 PM
  • Thanks, Hillary Cotter, that's exactly what I was seeking.
    Tuesday, September 25, 2018 6:32 PM
  • Hi .theDBguy,

    I am creating queries to store in a table that can then be looped through and executed.  These are validation queries to be executed against tables, they're to be written by QA people (not SQL people), and the single quotes cause problems on insert for the QA people.

    Hi,

    Thank you for the explanation. Hopefully the current solution (using a unit separator) doesn't cause a different problem for you down the road.

    Good luck with your project.

    Tuesday, September 25, 2018 6:53 PM
  • I have no idea what a unit separator is, I sure hope it works, too.  Please let me know if you have another suggestion.
    Tuesday, September 25, 2018 11:35 PM
  • I have no idea what a unit separator is, I sure hope it works, too.  Please let me know if you have another suggestion.

    Take a step back and rethink what you're doing.

    I'm not sure what exactly it is you are trying to achieve but I'm fairly confident this isn't the best way to go about it.

    Wednesday, September 26, 2018 2:31 AM
  • disssss, 

    Thanks for the input.  I'm wondering which part of my description leads you to believe this isn't the best way to go about it.  I'm fairly confident that you're right, mostly because I couldn't find any search results for "storing data validation queries in a table."  The only other alternative I can think of is to type each of those validation queries into a stored procedure, but this gives us no easy way to prioritize them, disable/enable them, etc.  I'm very interested in hearing other ideas.

    Thanks,
    Eric B.

    Wednesday, September 26, 2018 3:20 PM