CODEWITHSUNDEEP
sqlsql-servert-sqlreplacetrim
user1296762
user1296762

Reputation: 512

case statement to delete extra spaces only when there is some

I replace all blanks with @ using this

SELECT *,  REPLACE(NAME,' ','@') AS NAME2

which results miss@test@blogs@@@@@@@@@@@@@@ (different number of @s dependent on length of name!

I then delete all @ signs after the name using this 

select *, substring(Name2,0,charindex('@@',Name2)) as name3

which then gives my desired results of, for example MISS@test@blogs

However some wheren't giving this result, they are null. This is because annoyingly some rows in the sheet I have read in dont have the spaces after the name.

is there a case statement i can use so it only deletes @ signs after the name if they are there in the first place? Thanks

Upvotes: 1

Views: 1921

Answers (5)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

select name2, left(name2,len(name2)+1-patindex('%[^@]%',reverse(name2)+'.'))
from (
  SELECT *, REPLACE(NAME,' ','@') AS NAME2
  from t
) x;

Check this SQL Fiddle

For posterity, sample table:

create table t (name varchar(100));
insert t select 'name@name@ne@@@'
union all select '@name@name'
union all select 'name name hi   '
union all select 'joe public'
union all select ''
union all select 'joe'
union all select 'joe   '
union all select null
union all select '   leading spaces'
union all select '   leading trailing  ';

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

try this:

Declare @t table (name varchar(100),title varchar(100),forename varchar(100))
insert into @t
values('a  b   c','dasdh  dsalkdk  asdhl','asd dfg sd')


SELECT REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(name)),'  ',' '+CHAR(7)),CHAR(7)+' ','')  ,CHAR(7),'') AS Name,

REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(title)),'  ',' '+CHAR(7)),CHAR(7)+' ','')  ,CHAR(7),'') AS title,

REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(forename)),'  ',' '+CHAR(7)),CHAR(7)+' ','')  ,CHAR(7),'') AS forename

FROM @t WHERE
(CHARINDEX('  ',NAME) > 0 or CHARINDEX('  ',title) > 0 or CHARINDEX('  ',forename) > 0)

SQL Fiddle Demo

Upvotes: 2

aledpardo
aledpardo

Reputation: 761

I had this same problem some days ago.

Well actually, there's a quickly way to subtract the spaces from both the begin and end inside strings. In SQL Server, you can use the RTRIM and LTRIM for this. The first one supresses spaces from right side and the second supresses from left. But, if in your scenario also may exists more than one space in the middle of the string I sugest you take a look on this post on SQL Server Central: http://www.sqlservercentral.com/articles/T-SQL/68378/

There the script's author explain, in details, a good solution for this situation.

Upvotes: -1

E.J. Brennan
E.J. Brennan

Reputation: 46859

Don't quite understand the question, but if the problem is there is not spaces after some names, can't you do this first:

SELECT *,  REPLACE(NAME+' ',' ','@') AS NAME2

i.e., add a space to all names right off the bat?

Upvotes: 0

Andomar
Andomar

Reputation: 238116

The function rtrim can be used to remove trailing spaces. For example:

select replace(rtrim('miss test blogs             '),' ','@')
-->
'miss@test@blogs'

Example at SQL Fiddle.

Upvotes: 2

Related Questions