Updating table itself on SQL SERVER and use of CHARINDEX, SUBSTRING and LEN SQL built in functions.

In this article, i will be show you the full example how you can able to update single table itself by using some built in SQL SERVER system function.In this example, i will present you the problem that i faced and how i solved it. The problem is that when i have saved data on my table it also joined unwanted commas at the last of email, which will be one of the column on my table.For this first of all, i am going to create a table and insert some data on it.

CREATE TABLE table1(id INT IDENTITY(1,1) NOT NULL,email NVARCHAR(256))

INSERT INTO table1(email) values('kamal@yahoo.com')

INSERT INTO table1(email) values('kamal1@yahoo.com,')

INSERT INTO table1(email) values('kamal2@yahoo.com')

INSERT INTO table1(email) values('kamal3@yahoo.com,,')

INSERT INTO table1(email) values('kamal4@yahoo.com,')

At before, when i was not running below updated query, the table has data like..

ID	EMAIL
1	kamal@yahoo.com
2	kamal1@yahoo.com,
3	kamal2@yahoo.com
4	kamal3@yahoo.com,,
5	kamal4@yahoo.com,

And after that i need to remove comma from the email and save it on same table, to overcome from this problem i wrote the following query.

UPDATE a

SET a.email = substring(b.email, 1, CASE

                  WHEN charindex(',', b.email) > 0

                        THEN charindex(',', b.email) - 1

                  ELSE len(b.email)

                  END)

FROM table1 AS a

INNER JOIN table1 AS b ON a.id = b.id

This SQL query remove the ',' from email and i got the required result.

ID	EMAIL
1	kamal@yahoo.com
2	kamal1@yahoo.com
3	kamal2@yahoo.com
4	kamal3@yahoo.com
5	kamal4@yahoo.com

Alternatives:- You can get the same result by using cursor but i would not recommended it.

DECLARE @id INT

Declare @email nvarchar(256)

DECLARE myCursor CURSOR

FOR SELECT id,email FROM table1

OPEN myCursor

FETCH NEXT FROM myCursor

      INTO @id, @email

WHILE @@FETCH_STATUS = 0

BEGIN

            UPDATE table1

            SET email = substring(@email, 1, CASE

                  WHEN charindex(',', @email) > 0

                        THEN charindex(',', @email) - 1

                  ELSE len(@email)

                  END)

            WHERE id=@id

            FETCH NEXT FROM myCursor

              INTO @id, @email

END

CLOSE myCursor

DEALLOCATE myCursor