remove white spaces
-
i have some records which has a field which contains hyphen and spaces in between.
example :
34-123 45 67
i want to remove those spaces and hyphen from the fields in those records.
i.e records filed will be having
341234567 // without space
problem
--------
Problem is how do i find which records have the filed which conatains hyphen and spaces in between ? and how do i update that with removing hyphen and whitespaces ?
can you please tell me the SQL query ?
thank you
-
update tablename set fieldname = replace(replace(fieldname, '-', ''), '-', '')
-
1. i want to remove both the hyphen and whiite space
2. but there are 10000 records which has has got this trouble in that field (Phone_Number). do you mean , i should run your SQL query 10000 times !!
-
SELECT REPLACE(REPLACE(COLUMN_NAME, ' ', ''), '-', '') ASCOLUMN_ALIAS
FROM TABLE_NAME
subban -
SELECT REPLACE(REPLACE(COLUMN_NAME, ' ', ''), '-', '') ASCOLUMN_ALIAS
FROM TABLE_NAME
SELECT ! i dont want to display .
ASCOLUMN_ALIAS ? what is this ?
I AM CONFUSED.
ALL i need is to remove the spaces and hyphen from my Phone_Number field of my Table.
There are more than 10000 records. some records have this problem . i want to correct those.
what i should do ?
-
UPDATE TABLE_NAME SET COLUMN_NAME=REPLACE(REPLACE(COLUMN_NAME, ' ', ''), '-', '')
WHERE TABLE_NAME IS THE NAME OF THE TABLE
COLUMN_NAME IS THE NAME OF THE CCOLUMN WITH IN THE TABLE WHICH U NEED TO CHANGE
subban -
"1. i want to remove both the hyphen and whiite space
2. but there are 10000 records which has has got this trouble in that field (Phone_Number). do you mean , i should run your SQL query 10000 times !!"
1. The query initially provided will achieve this. You'll notice it has the REPLACE function twice, once for the hyphen and once for the space.
2. Running the query as-is will update every record in the table. If you don't want to update all the records you can add applicable criteria using the WHERE clause. Check Books Online for information on CHARINDEX, or PATINDEX, and use whichever suits your needs.
--------------------
Colt 45 - the original point and click interface
-
Just one additional point. You'll probably have to investigate the front-end application (or whereever the data originates) to make sure it is functioning properly as well. If the front-end is not fixed then you will be re-executing this query again (and again). What I'm trying to say is that you've got a fix for the database now you have to fix the root cause of the bad data problem too.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply