Tag: SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I recently ran across some people discussing how to log some information in a script. One person was using PRINT, which I often use for quick checks, but someone else noted the RAISERROR works well, and you can customize messages.

For example, I can have this type of script:

DECLARE @d VARCHAR(20);
-- do stuff
SELECT @d = CAST( SYSDATETIME() AS VARCHAR(20));

RAISERROR('%s - something happened at this time', 0, 1, @d)

This allows me to add information into an error message. I can certainly construct @d with other stuff and then use that in PRINT, but I could get out of order messages. If  I add NOWAIT, I can ensure my messages get returned immediately.

There are lots of options with RAISERROR, which I still use in place of THROW at times. While I like THROW, I think it doesn’t always give me the options I want for error handling, such as logging to the Windows lots.

SQL NewBlogger

When I saw this, I realized that I didn’t know, or remember, some of the ins and outs of RAISERROR, so I spent a few minutes looking through docs and playing with the code. I then wrote this quick post to help me remember a bit more.

Short and quick is a good way to structure posts. I didn’t walk about all the options or ways I can use things. I’ll do some of that in another post.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?

This post covers the basics of this task.

Scenario

Suppose you have some strings in a table, and they contain multiple values. I see this often when application developers serialize some data. For example, I might create a table like this:

CREATE TABLE mytable
(   mykey INT NOT NULL CONSTRAINT mytablepk PRIMARY KEY
   , myval VARCHAR(100));
GO

INSERT dbo.mytable
     (mykey, myval)
VALUES
     (1, 'apple,pear,banana')
   , (2, 'pear,peach,melon');
GO

SELECT * FROM dbo.mytable AS m;

This has a few rows of multiple values in a field.

Imagine now I need to change pear to grape in all rows. I want a simple solution to do this.

Solution

I have seen some people try to use complex substring calls paired with other functions to do this, but T-SQL gives you a really simple solution. We have a REPLACE() function that allows us to change a string without parsing it.

The simple way to do this is like this:

SELECT
      m.mykey
    , m.myval
    , REPLACE(m.myval, 'pear', 'grape') AS newstring
FROM dbo.mytable AS m;

Always run a SELECT before an UPDATE, but in this case, I can see that pear has been removed and grape is in its place.

REPLACE() works by passing in a string as the first parameter, then a second string to search for, pear in this case, and finally a replacement. I could then put together an UPDATE statement to change my table.

UPDATE dbo.mytable
  SET myval = REPLACE(myval, 'pear', 'grape')
FROM dbo.mytable AS m;

If I run this, the results shown above for newstring will replace the myval string for all rows.

SQLNewBlogger

This is an example of a basic type of T-SQL solution that is simple, with a quick explanation. I answered this for someone and then spent 10 minutes writing this up.

A good story to have ready for an interview.