String Manipulation In SQL Server And Visual Foxpro SQL

One of my friend asked me about writing a query in SQL Server which can alter a portion of word or characters in a field in a table.

Since he is a VFP guy, I showed him how I could do this both in VFP and in SQL Server so he could understand it easily.

In Visual Foxpro, we can either write xbase-type codes or make use of VFP-SQL and use UPDATE in a query to change a portion of words in a field.

An example in VFP would be:

CREATE CURSOR TestTable (PKID int NOT null, description char(50) null)

INSERT INTO TestTable (PKID, DESCRIPTION) ;
VALUES (1,'This is a test.')

INSERT INTO TestTable (PKID, DESCRIPTION) ;
VALUES (2,'This is a test again.')

INSERT INTO TestTable (PKID, DESCRIPTION) ;
VALUES (3,'Not part of the test.')


These VFP commands should give us a table structure with records as shown below:

TestTable records.

Now, we're going to update the field 'DESCRIPTION' and try to change the word "test" with "sample." A simple update query mixed with a STRTRAN() function (which is a VFP native function) to alter the values in the all the rows in the DESCRIPTION column.


UPDATE TestTable ;
SET Description = STRTRAN(description,'test','sample')

This query  should give us this result:

TestTable records after the update query.

Take note that it is not necessary to affect all the rows in the DESCRIPTION column and we can normally filter out the row which we only want to change. The example below should change the record with PKID=2 in which we would change the word "sample" back to "test" again.


UPDATE TestTable ;
SET Description = STRTRAN(description,'sample','test') ;
WHERE PKID = 2

TestTable records after update query with a "WHERE" filter clause.

On the other hand, in SQL Server, here's an example of such:

CREATE TABLE [dbo].[TestTable](
[PKID] [int] NOT NULL,
[Description] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
go

INSERT INTO TestTable (PKID, DESCRIPTION)
VALUES (1,'This is a test.');
go

INSERT INTO TestTable (PKID, DESCRIPTION)
VALUES (2,'This is a test again.');
go

INSERT INTO TestTable (PKID, DESCRIPTION)
VALUES (3,'Not part of the test.');

go

The same structure of TestTable is created in SQL Server which should look like this:

TestTable records, created in SQL Server.

Altering a part of a text value of a field in an SQL Server table can be done similar to what we have used in VFP but instead of using STRTRAN() function, we will use the REPLACE() function instead. These two functions work the same way although they are called differently.

UPDATE TestTable ;
SET Description = REPLACE(description,'test','sample')
go

SQL Server's TestTable records after  running the Update query with a REPLACE() function.

Like what we did in VFP, we can also add a criteria to the update query to affect only our record of choice. Adding the WHERE clause in an UPDATE query is considerable especially in the real work because the last thing you will ever want is to mess up all the records in a column instead of the intended ones.