Reputation: 33

Replace in SQL Server

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

Upvotes: 2

Answers (4)

Reputation: 2011

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)

Upvotes: 0

Reputation: 5453

You can try this :

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

Upvotes: 0

Reputation: 2475

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.

Upvotes: 3

Reputation: 50163

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

select left(P_BIRTH, 4)

Upvotes: 0