Removing a fixed character in the middle of a string-CodePudding

I am trying to determine the best/correct SQL Server function to remove a character in the middle of a string.

Values:

CAAxxB
CBBxx
CBAxxB
CBBxxB
  ^

I want to remove the character in the third position only (with no space) and leave the remaining characters (xx are always numbers)

Result:

CAxxB
CBxx
CBxxB
CBxxB

CodePudding user response：

You can use STUFF

  SELECT STUFF(ColumnName, 3, 1, '')

Fiddle sample

CodePudding user response：

Three options:

DECLARE @test varchar(10) = 'CAAxxB'
SELECT 
    substring(@test, 1,2)   substring(@test, 4,50) -- Make sure this last value is large enough
   ,substring(@test, 1,2)   right(@test, len(@test)-3)
   ,stuff(@test, 3,1,'')

CodePudding user response：

You can do something like

select substring(val, 1, 2)   substring(val, 4, len(val))
from txt

Example data-source and structure:

create table txt(val varchar(32));

insert into txt(val) values
('123456'),
('789012'),
('345678'),
('901234'),
('123'),
('12'),
('567890');

SQL Fiddle: http://sqlfiddle.com/#!18/415a9/1

Explanation: substring returns a chunk of a textual value starting from an index up until a certain length (indexing starts from 1). So, if we want to avoid the third character, then we concatenate two substring, the first containing the string up to the character to be omitted and the second starting from the next character up until the end.