Search code examples
sqlsql-servertrim

SQL server trimming string

I have a query, I want to Trim a column and get the only Right side values:

enter image description here

Here in the pic the result is like SubAccountCode and SubaccountName but user has entered code and name in SubaccountName. I want to trim the codes from Subaccountname and update the table. T

The query I have tried is mentioned below, but I think it's not so working:

 Select Substring(Subaccountname,8,20)as Name from #temp
Solution

  • There are several ways to do this. When looking at your example data the easiest way would be using an replace() in the update statement.

    Syntax: REPLACE ( string_expression , string_pattern , string_replacement )

    Example:

    UPDATE table_name
SET column2 = replace([column2], [column1], '')

    What this does it updates the column2 with the value from column2 where the value from column1 is replaced with '' nothing. In your example this does leave you with an unwanted space in front. You could trim this or try as followed:

    UPDATE Test
SET [SubAccountName] = replace([SubAccountName], [SubAccountCode] + ' ', '')

    If the SubAccountCode could be different from the code in the SubAccountName and you only want to remove the first 8 characters (if you are sure it is always the first 8) you can use:

    UPDATE YourTable SET SubAccountName = RIGHT(SubAccountName, LEN(SubAccountName) - 7)

    Example script:

    create table test (
    SubAccountName varchar(100),
    SubAccountCode varchar(100)
    )

insert into test (SubAccountCode, SubAccountName) VALUES
(1234567, '1234567 AUBC' ),
(1234467, '1234467 AUBC' ),
(1235567, '1235567 AUBC' )

select * from test -- Check that the data is like your example.

UPDATE Test SET SubAccountName = RIGHT(SubAccountName, LEN(SubAccountName) - 8)

select * from test -- Check that the result is like your wanted result.

drop table test -- Cleanup the test table.