select statement

Question

select statement

textwhisperer

SSC Veteran

Points: 280
More actions
June 20, 2010 at 9:30 am

#140946

Help-My brain is fried- I just can't figure out the last step (six).
Write a select statement that returns these columns from the vendors table:
1The vendor_name column
2The vendor_name column in all caps
3The vendor_phone column
4The last four digits of each phone #
5The second word in each vendor name if there is one; otherwise, blanks
6The vendor phone column with the parts of the number separated by dots as in 555.555.5555
This is what I have so far:
Select vendor_name, UPPER (vendor_name), vendor_phone,
SUBSTR(vendor_phone, 11,4),
SUBSTR(vendor_name, (INSTR (vendor_name, ' ') + 1)),
REPLACE (vendor_phone, '-', '.')
FROM vendors

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Is this homework?

It looks like you have step 6 - REPLACE (vendor_phone, '-', '.'). What's that not doing that it should?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

textwhisperer SSC Veteran Points: 280 More actions · Answer 2

What I entered returns "(555) 555.5555"

I need to get rid of the parentheses & add a dot after the area code

It's supposed to yield "555.555.5555"

BTW-This actually could have been H.W. However, I dropped the online course because of poor instruction. Now I'm studying informally because I still want to pursue DBA. Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You need three more REPLACE statements then, nested. One to replace the ( with an empty string '' and one to replace the ) with an empty string, then a third to replace the space with a .. Same way you have for the first REPLACE, nest them so that the output of the first is the expression to be searched for the second, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ashish 10950 SSC-Addicted Points: 422 More actions · Answer 4

Try

SELECT Replace(REplace(REplace(Phone_number,'(',''),')','.'),'-','.')

It will solve your replace issue of Phone_number