erikTsomik
asked on
Writing a SQL query to consolidate 2 fileds into the same format
I have a table users which consists 2 column phoneNumber and Contact (there more columns into that table userID).
PhoneNUmber is an old Field (in the diffferent format xxx-xxx-xxxx,xxx.xxx.xxxx, (xxx)xxx-x xxx).Wjhat I want to do is to take that field and format it into this format (xxx-xxx-xxxx) and put it into the column Contact
PhoneNUmber is an old Field (in the diffferent format xxx-xxx-xxxx,xxx.xxx.xxxx,
ASKER
But that will give a mixture of numbers .What I need to is to take the phoneNumber column and update to format xxx-xxx-xxxx and put it into the Contact column
can you share more data with us !!!!!!
You'll just end up with different formats again!
Strip the formatting from the stored data. Formatting chars are not actual data in this case.
Add a formatting code, if you need it, to determine how to format the data when it is output/displayed.
C'mon, wouldn't format: xxxxxxxxxx be a lot simpler and more consistent all the way around!?
Strip the formatting from the stored data. Formatting chars are not actual data in this case.
Add a formatting code, if you need it, to determine how to format the data when it is output/displayed.
C'mon, wouldn't format: xxxxxxxxxx be a lot simpler and more consistent all the way around!?
ASKER
How would I strip it off can you give me an example
You could use a function that stripped every nonnumeric char.
Or, if your data is reasonably clean, just embed REPLACEs:
UPDATE dbo.tablename
SET contact = REPLACE(REPLACE(REPLACE(RE PLACE(phon e_number, '-', ''), '.', ''), '(', ''), ')') --...
WHERE
contact IS NULL OR contact = ''
Then add a CHECK to the table to prevent bad data from getting in:
ALTER TABLE table_name
ADD CONSTRAINT table_name__CK_contact CHECK(contact NOT LIKE '%[^0-9]%')
Or, if your data is reasonably clean, just embed REPLACEs:
UPDATE dbo.tablename
SET contact = REPLACE(REPLACE(REPLACE(RE
WHERE
contact IS NULL OR contact = ''
Then add a CHECK to the table to prevent bad data from getting in:
ALTER TABLE table_name
ADD CONSTRAINT table_name__CK_contact CHECK(contact NOT LIKE '%[^0-9]%')
ASKER
will constraint remove strip off unwanted data
No, it will prevent non-numeric char(s) from being INSERTed into the contact column.
For example:
create table table_name ( contact varchar(30) not null )
ALTER TABLE table_name
ADD CONSTRAINT table_name__CK_contact CHECK(contact NOT LIKE '%[^0-9]%')
go
insert into table_name values('111')
insert into table_name values('12345678990898098' )
insert into table_name values('1234b')
go
select * from table_name
If you wanted, you could also add a check to verify that the length is either 7 or 10 digits.
create table table_name ( contact varchar(30) not null )
ALTER TABLE table_name
ADD CONSTRAINT table_name__CK_contact CHECK(contact NOT LIKE '%[^0-9]%')
go
insert into table_name values('111')
insert into table_name values('12345678990898098'
insert into table_name values('1234b')
go
select * from table_name
If you wanted, you could also add a check to verify that the length is either 7 or 10 digits.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want to retrieve "xxx-xxx-xxxx" from "xxx-xxx-xxxx,xxx.xxx.xxxx
select left(phoneNumber,12) from users ;