Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Remove special characters

Posted on 2016-09-06
5
Medium Priority
?
61 Views
Last Modified: 2016-09-10
Greetings!

I would like some assistance to remove the | sign ONLY, if the Category IDs start with |

Example: Per highlighted screenshot, I would like to remove the | in the front of the column. I would like to leave the | for the rest to help separate the numbers. Thanks!

 remove pipeline
0
Comment
Question by:Member_2_7967487
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41787313
try like:
select *, 
case when charindex('|',CategoryIDs)=1 then substring(CategoryIDs,charindex('|',CategoryIDs)+1,len(CategoryIDs)-1) else CategoryIDs end New_CategoryIDs from Items

Open in new window

or
select *, 
case when charindex('|',CategoryIDs)=1 then substring(CategoryIDs,2,len(CategoryIDs)-1) else CategoryIDs end New_CategoryIDs from Items

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 41787370
I think using left is more effective:
select *, case when left(CategoryIDs,1) <> '|' then CategoryIDs else substring(CategoryIDs,2,8000) end CategoryIDs2 from Items

Open in new window

0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41787379
Please test this in a DEV environment before running in Production:
UPDATE Items
SET CategoryIDs = SUBSTRING (CategoryIDs ,2, LEN(CategoryIDs)-1)  
WHERE LEFT(CategoryIDs,1) = '|'

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41788000
You should use LIKE, because it is "sargable", rather using any function on the column, which is not sargable.  Thus, indexes could be far more efficiently used with LIKE.

I've also found STUFF to be more efficient than SUBSTRING to remove a char(s).

Therefore, my code would be:

UPDATE ...
SET CategoryIDs = STUFF(CategoryIDs, 1, 1, '')
WHERE CategoryIDs LIKE '|%'
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 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