Replace in SQL Server

Question

I want to replace all birth that I have from EX : 199500 to 1995 so how can I remove all 2 zeros in my column?

I used this:

select P_BIRTH from city.dbo.personsuly replace(P_BIRTH,00, ,);

SQL_M · Accepted Answer · 2018-01-23 08:06:43Z

3

UPDATE city.dbo.personsuly

SET P_BIRTH = LEFT(P_BIRTH, 4)

When you use LEFT function, you can select a set of charachters from the original column.

answered Jan 23, 2018 at 8:04

SQL_M

2,4852 gold badges18 silver badges32 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2018-01-23 08:04:18Z

0

You could use left() function instead of using replace()

select left(P_BIRTH, 4)

answered Jan 23, 2018 at 8:04

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Md. Suman Kabir · Accepted Answer · 2018-01-23 08:16:19Z

0

You can try this :

select stuff(P_BIRTH, charindex('00', P_BIRTH), 2, '') from city.dbo.personsuly

answered Jan 23, 2018 at 8:16

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

Pawan Kumar · Accepted Answer · 2018-01-23 08:30:32Z

0

You can also try like this -

UPDATE city.dbo.personsuly
SET P_BIRTH = P_BIRTH / 100

Example ..This can also work with string data types.

SELECT 199500  / 100

-----------
1995

(1 row affected)

answered Jan 23, 2018 at 8:30

Pawan Kumar

2,02112 silver badges12 bronze badges