I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first in some drop down lists. I am trying to figure out how I can remove these periods when they are the first character of the column with out removing periods later in the name (like for Middle Initials). This change is being made during and INSERT INTO SELECT command.

  • Easy to do using a case statement and substring()/len(), surely? – Philᵀᴹ May 22 '14 at 20:30

One option is using a CASE statement, such as this one:

CASE WHEN LEFT(MyColumn,1)='.' THEN RIGHT(MyColumn, LEN(MyColumn)-1) ELSE MyColumn END

Try to minimize using too many scalar functions, which could be performance hog if you are dealing with large amount of data. Following example uses STUFF function on a subset of data with union all table operator to minimize the performance cost.

insert into newtable
(newname, col2, col3)
select STUFF(oldname,1,1,''), col2, col3   
from oldtable
where oldname like '.%'
union all
select oldname, col2, col3
from oldtable
where oldname not like '.%';

The query engine will not use index for the portion with STUFF function, that is before the union all operator. The second part of the query does not have any scalar function and the first character in the like operator is not a wildcard character, so the query engine can utilize the indexes for this query, thus improving the overall query performance.

  • I'd imagine that not like '.%' is not going to be a very efficient seek though; unless it matches a small percentage of the table, and particularly if the index used to seek does not cover the other columns required for output, it is likely going to be better for SQL Server to scan anyway. – Aaron Bertrand May 29 '14 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.