Solved

Remove special characters

Posted on 2016-09-06
5
56 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 51

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 69

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 49

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 69

Accepted Solution

by:
Scott Pletcher earned 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
SQL Query Task 11 43
sql server cross db update 2 20
TSQL Assignining CTE column in to a variable 3 7
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

756 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