Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Easy to do using a case statement and substring()/len(), surely? –  Phil May 22 at 20:30

2 Answers 2

up vote 4 down vote accepted

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
share|improve this answer

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.

share|improve this answer
    
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 at 2:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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