<div>
try like:<br />

<pre><code id="code-20-41787313-1">select *, 
case when charindex('|',CategoryIDs)=1 then substring(CategoryIDs,charindex('|',CategoryIDs)+1,len(CategoryIDs)-1) else CategoryIDs end New_CategoryIDs from Items</code></pre>
or<br />

<pre><code id="code-20-41787313-2">select *, 
case when charindex('|',CategoryIDs)=1 then substring(CategoryIDs,2,len(CategoryIDs)-1) else CategoryIDs end New_CategoryIDs from Items</code></pre>
</div>


try like:


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

or


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

<div>
I think using left is more effective:<br />

<pre><code id="code-20-41787370-1">select *, case when left(CategoryIDs,1) &lt;&gt; '|' then CategoryIDs else substring(CategoryIDs,2,8000) end CategoryIDs2 from Items</code></pre>
</div>


I think using left is more effective:


select *, case when left(CategoryIDs,1) <> '|' then CategoryIDs else substring(CategoryIDs,2,8000) end CategoryIDs2 from Items

<div>
Please test this in a DEV environment before running in Production:<br />

<pre><code id="code-20-41787379-1">UPDATE Items
SET CategoryIDs = SUBSTRING (CategoryIDs ,2, LEN(CategoryIDs)-1)  
WHERE LEFT(CategoryIDs,1) = '|'</code></pre>
</div>


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) = '|'

<div>
<div class="content wysiwyg-content">
Greetings!<br />
<br />
I would like some assistance to remove the | sign ONLY, if the Category IDs start with |<br />
<br />
<b>Example</b>: 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!<br />
<br />
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2016/09_w37/1115543/pipeline.jpg" target="_blank" title="remove pipeline (181 KB)"><img alt="remove pipeline" class="file-block lazyload" style="max-width: 586px;" data-src="https://filedb.experts-exchange.com/incoming/2016/09_w37/1115543/pipeline.jpg" /></a>
</div>
</div>


pipeline.jpg

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 special characters

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.