Multiple parameters in Replace SQL Server

Question

I am using SQL Server 2016. I have a column A that has values such as C20.1 and C20.2. I just want to remove the postfix values (.1 and .2) and keep C20 only.

Column A
---------
C20
C20.1
C20.2

Output should be like this:

Column B
--------
C20
C20
C20

I tried this SQL statement:

SELECT 
    REPLACE (ColumnA, '.1', '.2', '')

but this query throws an error when I pass 4 parameters.

Tim Biegeleisen · Accepted Answer · 2021-08-25 05:38:37Z

2

We could phrase your requirement as saying that you want to retain the input string up, but not including, the first dot, or if not dot be present, then retain the entire string. This gives us:

SELECT
    val,
    CASE WHEN CHARINDEX('.', val) > 0
         THEN SUBSTRING(val, 1, CHARINDEX('.', val) - 1)
         ELSE val END AS val_out
FROM yourTable;

Demo

answered Aug 25, 2021 at 5:38

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2021-08-25 05:35:39Z

1

You can use substring()

select substring(columnA,0,4)
from table

answered Aug 25, 2021 at 5:35

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

1 Comment

Gordon Linoff Over a year ago

The best expression is left(columnA, 4).

Collectives™ on Stack Overflow

Multiple parameters in Replace SQL Server

2 Answers 2

Demo

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related