Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
caravanpunk
Starting Member
20 Posts |
 Posted - 2011-06-14 : 15:05:47
|
| here is the table.select * from person--- the person.homephone has0214-223-2223213.234.2121 now I want to create output in this wayIf 0 then 999999999214-223-2223=2142232223213.223.2323=2132232323 here is my scriptselectCASE WHEN LEN(PERSON.HomePhone)=0 THEN '9999999999' ELSE ISNULL(REPLACE(RTRIM(LTRIM(PERSON.HomePhone)),'-',''),'9999999999') END from person from this i can get when homephone=0 then 999999999, when 214-223-2223 then 2142232223.I am trying to get that 3rd one too....when 213.223.2323 then 2132232323, inside that sql statement, can anyone help me on that??Thanks in advance |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-14 : 15:14:10
|
| CASE WHEN LEN(PERSON.HomePhone)=0 THEN '9999999999' ELSE ISNULL(REPLACE(REPLACE(RTRIM(LTRIM(PERSON.HomePhone)),'-',''),'.',''),'9999999999') END |
 |
|
|
caravanpunk
Starting Member
20 Posts |
Posted - 2011-06-14 : 15:15:37
|
| great, works, thanks a lot |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-15 : 13:52:06
|
| People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it. Let's use the telephone industry standard and add a dummy all-9's phone number instead of a NULL. And, please leave the dashed out; display formats are done in the front end. CREATE TABLE Personnel ---collective noun for table name(emp_id CHAR(10) NOT NULL PRIMARY KEY,. home_phonenbr CHAR(16) DEFAULT'999999999999' NOT NULL CHECK (home_phonenbr LIKE '[0-9][0-9] .. [0-9]' ..);Your request is silly because the table take car of it with DRI. You trim, pad and scrub the phone numbers in the ETL process, not in the schema. The schema gets only clean data. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
sql script