CODEWITHSUNDEEP
sql-server-2008
user2869233
user2869233

Reputation: 111

How to replace delimiters from a string in SQL Server

I have the following data 

abc
pqr  
xyz,
jkl mno

This is one string separated by delimiters like space, new line, comma, tab. There could be two or more consecutive spaces or tabs or any delimiter after or before a word.

I would like to be able to do the following

  1. Get the individual words removing all leading and trailing delimiters off it
  2. Append the individual words with "OR"

I am trying to achieve this to build a T-SQL query separated by OR clause.

Thanks

Upvotes: 1

Views: 5473

Answers (1)

Mohammed Swillam
Mohammed Swillam

Reputation: 9242

I think you can achieve what you need (although I think using a programming language is way better) using just SQL, here is my approach.

Kindly note that I will just handle commas, newlines and multiple-spaces, but you can simple follow using the same technique to remove the rest of your undesired characters

so let's assume that we have a table names ExampleData with a column named DataBefore and another called DataAfter.

DataBefore: has the line value that you want to clean DataAfter: will host the cleaned text

First we need to trim the preceding & leading space(s) from the text

Update ExampleData set DataAfter = LTRIM(RTRIM(DataBefore))

Second, we should clean all the commas, and replace them with spaces (doesn't matter if we will end up with many spaces together)

Update ExampleData set DataAfter = replace(replace(DataAfter,',',' '),char(13),' ')

This is the part in which you may continue and remove any other characters using the same technique, and replace it by a space

So far we have a text that has no spaces before or after, and every comma, newline, TAB, dash, etc character replaced by a space, let's continue our cleaning procedure.

We can now safely move on to replace the spaces between words with just one, this is made by using the following SQL statement:

Update ExampleData set DataAfter = replace(replace(replace(DataAfter,' ','<>'),'><',''),'<>',' ')

as per your needs, we need to place an OR between each word, this is achievable with this SQL statement:

Update ExampleData set DataAfter = replace(replace(replace(DataAfter,' ','<>'),'><',''),'<>',' OR ')

we are done now, as a final step that may or may not make a change, we need to remove any space at the end of the whole text, just in case an unwanted character was at the end of the text and as a result got replaced by a space, this can be achieved by the following statement:

Update ExampleData set DataAfter = RTRIM(DataAfter)

we are now done. :)

as a test, I've generated the following text inside the DataBefore column: this is just a, test, to be sure, that everything is, working, great .

and after running the previous commands, ended up with this value inside the DataAfter column: this OR is OR just OR a OR test OR to OR be OR sure OR that OR everything OR is OR working OR great OR .

Hope that this is what you want, let me know if you need any extra help :)

Upvotes: 1

Related Questions