CODEWITHSUNDEEP
sqlsql-server
BradM
BradM

Reputation: 21

Removing a fixed character in the middle of a string

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

Upvotes: 1

Views: 2554

Answers (3)

Lajos Arpad
Lajos Arpad

Reputation: 76424

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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

Upvotes: 0

huMpty duMpty
huMpty duMpty

Reputation: 14460

You can use STUFF

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

Fiddle sample

Upvotes: 4

Related Questions