how to use trim to update the column values.

Question

1.00/5 (1 vote)

See more:

I want remove first zero in areacode column..

areacode areaname

A000001 bangalore
A000002 delhi
A000003 mumbai
A000004 kolkata
A000005 Hyderbad

result should be like this
A00001 bangalore
A00002 delhi
A00003 mumbai
A00004 kolkata
A00005 Hyderbad

How to do this pls tell me

Posted 27-Feb-15 20:33pm

Member 11337367

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-02-27T21:02:00

Solution 1

Try:

SQL

SELECT SUBSTRING(areacode, 1, 1) + SUBSTRING(areacode, 3, 1000) AS Areacode, areaname FROM MyTable

"I want to update that column .not only select"

So tell us exactly what you want!
It's a trivial mod anyway:

SQL

UPDATE MyTable SET areacode = SUBSTRING(areacode, 1, 1) + SUBSTRING(areacode, 3, 1000)

But backup first incase you make a mistake: always backup before you run an UPDATE without a WHERE clause!

Posted 27-Feb-15 21:02pm

OriginalGriff

Updated 27-Feb-15 22:01pm

v2

Comments

Member 11337367 28-Feb-15 3:50am

I want to update that column .not only select

OriginalGriff 28-Feb-15 4:01am

Answer updated

Member 11337367 28-Feb-15 4:10am

Could you tell me how this query will works..what sub string command will l do..

OriginalGriff 28-Feb-15 5:58am

Um...did you look at the updated answer?

Member 11337367 28-Feb-15 6:04am

Ya.That is updated..

OriginalGriff 28-Feb-15 6:20am

And?
Still have a problem? :laugh:

Member 11337367 28-Feb-15 6:34am

No..I just want to know how your query is works..

OriginalGriff 28-Feb-15 6:47am

It looks at each row in turn (as UPDATE always does) and takes the existing column content:
A000001
It then uses the SQL SUBSTRING function twice:
First time it pulls out one character from the start of the string: "A"
Second time it pulls out 1000 characters from the third position: "00001"
(since the input string from the column is less than 1000 characters, it just pulls the rest of the string)
It then concatenates the two strings, and updates the column with the new value.

how to use trim to update the column values.

1 solution

Solution 1

Add your solution here

Preview 0

how to use trim to update the column values.

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us