Removing text

Question

Hello

I am using SQL Server 2014.

A data field returns both a customer's name and their address as:

William Faulkner, 10, Smith Street, London,

Please can you advise on a way to remove the customer's name - 'William Faulkner' in this instance - and the first comma, so the output would be as:

10, Smith Street, London, N1

with many thanks

Answer 1

It depends on the definition of the data format. When it is fixed

value := customer-name comma-sign customer-address
comma-sign := ASCII 44
customer-address := literal
customer-name := literal without comma-sign

then it is a simple:

DECLARE @test TABLE (
    col VARCHAR(256)
);

INSERT INTO @test
VALUES ( 'William Faulkner, 10, Smith Street, London,' ) ,
       ( 'George West, 10, John street, George West, Texas' );

SELECT T.col ,
       LTRIM(SUBSTRING(T.col, CHARINDEX(',', T.col) + 1, 1024))
FROM   @test T;

In all real world scenarios I've seen, when people relied on data format by convention, there was in the end data violating the convention.
So splitting data is possible, but you cannot be sure that you have done it correctly.
Depending on the use-case and process behind, you need an instance to verify the results.

And any other possible format than the above will increase the risk of parsing problems and a bad data quality in the end.

Answer 2

Hi @Will Faulkner ，

Please refer to:

    create table test(col varchar(256))  
    insert into test values('William Faulkner, 10, Smith Street, London,')   
    select replace(col,left(col,charindex(',',col)),'') from test  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.