?
Solved

Remove Spaces in a field

Posted on 2009-04-23
15
Medium Priority
?
257 Views
Last Modified: 2012-05-06
I need to remove the spaces in the field called (qryAllocText) this field is basically an email that been copied into this field. When we try send the data using reporting services in brings up errors in converting the data into excel format.

Please can you advise what i need to do in the sql script to remove spaces and line breaks etc
Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
qryAllocText,
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname  + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey 
 
From CTS..Queries Q   
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
 
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory   
inner Join CTS..Categories C on C.catID = L.Category  
inner Join CTS..CallTypes T on T.ctKey = L.CallType 
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser   
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept  
 
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo

Open in new window

0
Comment
Question by:Davesm
  • 11
  • 4
15 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222131
have a look.



Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
rtrim(ltrim(qryAllocText)),
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname  + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey 
 
From CTS..Queries Q   
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
 
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory   
inner Join CTS..Categories C on C.catID = L.Category  
inner Join CTS..CallTypes T on T.ctKey = L.CallType 
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser   
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept  
 
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo

Open in new window

0
 

Author Comment

by:Davesm
ID: 24222155
Hi RiteshShar
Thanks for the assistance

When I try run the new script i get the following error

Msg 8116, Level 16, State 2, Line 3
Argument data type ntext is invalid for argument 1 of ltrim function.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222171
which SQL Server version are you using? it is working fine in my SQL Server 2005.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222175
sorry, ignore my previous post
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222209
since you are using nText, you can't use rtrim and ltrim, below link will help you.

http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222222
or you can alter your ntext column to nvarchar(max) in sql server 2005 to get maximum benifit of new data type. if you alter column, my first script will work.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222232
you can do something like this also.




Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
rtrim(ltrim(convert(nvarchar(max),qryAllocText))),
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname  + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey 
 
From CTS..Queries Q   
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
 
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory   
inner Join CTS..Categories C on C.catID = L.Category  
inner Join CTS..CallTypes T on T.ctKey = L.CallType 
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser   
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept  
 
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo

Open in new window

0
 

Author Comment

by:Davesm
ID: 24222264
Sorry to bug you but its now come up with the following  error

Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'max'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'as'.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222268
which sql server version are you using?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222278
if you are using sql server 2000 than this should work, I don't have sql server 2000 to check with.



Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
rtrim(ltrim(convert(nvarchar(5000),qryAllocText))) as 'qryAllocText',
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname  + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey 
 
From CTS..Queries Q   
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
 
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory   
inner Join CTS..Categories C on C.catID = L.Category  
inner Join CTS..CallTypes T on T.ctKey = L.CallType 
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser   
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept  
 
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo

Open in new window

0
 

Author Comment

by:Davesm
ID: 24222282
we using sql server 2000
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222286
what about my last query?
0
 

Author Comment

by:Davesm
ID: 24222315
this seems to be working fine will check how the data come out

Thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24222320
no problem. enjoy!!!!
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24222333
last query will remove space from starting of the content and at the end of content, if you want to remove all spaces, available in string than do use this one.
Select ctName,
C.catName,
Q.qryNo,
actQryNo,
qryAccNo,
qryNasRefNo,
qryAllocDept,
Department_name,
qryLogDept,
replace( convert(nvarchar(5000),qryAllocText),' ','') as 'qryAllocText',
qryFinalText,
qrySource,
qryMethod,
qryStatus,
E.Surname  + ' '+ E.First_Name as Employee,actUser,
Q.qryLogTime,
A.actTime,
actKey 
 
From CTS..Queries Q   
inner join (select Max(actKey) as MaxKey
,actQryNo as MaxactQryNo
from Actions
group by actQryNo) as MaxAction on MaxAction.MaxactQryNo = Q.QryNo
inner join Actions A on MaxAction.MaxKey = A.actKey
 
inner Join CTS..CallType_Category_Links L on L.ccID = Q.qryCallTypeCategory   
inner Join CTS..Categories C on C.catID = L.Category  
inner Join CTS..CallTypes T on T.ctKey = L.CallType 
inner Join CTS..Customers Cus on Cus.cstAccNo = Q.qryAccNo
Inner Join SYS04_Frontier..Frontier_sys04_Employees E on E.Logon_Name = Q.qryAllocUser   
Inner join SYS04_Frontier..Frontier_sys04_DEPARTMENTS D on D.ID = Q.qryAllocDept  
 
Where ctName in('Branch Banking Existing Business', 'Branch Banking New Business')
and Q.qryStatus IN('Pending','Unresolved')
Order by actQryNo

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question