If you work with databases, you will probably come across instances where you'll need to clean or modify textual data. SQL has a function for this, called REPLACE()—that is sure to help you out.
In this blog post, we will discuss what the REPLACE() function is, how it works, and common use cases—with examples.
REPLACE() Function in SQL?The
REPLACE() function in SQL is used to substitute all occurrences of a substring within a string with a new substring. REPLACE(original_string, substring_to_replace, new_substring)
- original_string: The string where replacements will be made.
- substring_to_replace: The part of the string you want to replace.
- new_substring: What you want to replace it with.
Select Replace('Hi coder how are you all?','all',' ') [Replaced String]
in above snippet you can see , all is replaced with space
Note:
REPLACE() is case-sensitive in most SQL dialects (like MySQL and PostgreSQL).The
REPLACE function is commonly used for data cleaning. In the example below, it's used to clean up a blog title by formatting it appropriately for use in a URL. Create FUNCTION dbo.fn_CleanBlogTitle (@BlogTitle NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @CleanedTitle NVARCHAR(255);
SET @CleanedTitle = @BlogTitle;
SET @CleanedTitle = REPLACE(@CleanedTitle, '!', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '#', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '$', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '^', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '&', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '*', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '(', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, ')', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '+', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '=', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '{', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '}', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '[', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, ']', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, ':', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, ';', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '"', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '?', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '<', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '>', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, ',', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '.', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '/', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '?', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '|', '');
SET @CleanedTitle = REPLACE(@CleanedTitle, '.', '');
SET @CleanedTitle = LOWER(@CleanedTitle);
RETURN @CleanedTitle;
END;
🙏 Thank you for reading!
Thank you for taking the time to read this blog!
If you have any questions or need help with something, feel free to drop a message in the comments or contact section. I’ll get back to you as soon as possible.
Happy Learning! 😊