MS SQL select question

berengar

Hi I need to create a SQL Select that extracts the following info:

Surname
First name
Address
Zipcode
Town
Prefix
Phonenumber
Product
Code

Where Code is a constant of value 56, Product is the outcome code and Prefix and Phonenumber are a split from the phone column of the campaign. The Business rule that controls the spliting is as follows.
Only fix lines must be splitted between prefix and phonenumber. Mobile phones should go completely to phonenumber.
Prefixes can be of 1 or 2 digits length
The valid prefixes are:
1Y Where Y ranges from 3 to 9
2
3
5X Where X ranges from 1 to 9
6
8
9
Here you have a couple of phone splitting examples:

Phone: 141234567 -> Fix line -> (14) 1234567
Phone: 401234567 -> Mobile -> () 401234567
______________________________

Can i use a JOIN to do this ? or can I use if statements and
code in a MS SQL select statement ?

any help appreciated

seamus

This is a college assignment, yes? Show us what you've got thus far. You're also going to need to expand a little more.

As best I understand your question, it's as simple as:
SELECT Surname, First name, Address, Zipcode, Town, Prefix, Phonenumber, Product, Code FROM table WHERE Code = 56

You'll need to define the following words for your question to make any sesne to us:
"outcome code"
"compaign"
"Business Rule"

berengar

yeah it is, you can ignore the business rules , outcome code etc.

All i want to know is can I manipulate SQL select statements in MsSQL
(if statements etc) so I can split the phone number into prefixes etc.

FSL

Yes you can have conditional select statements using Case or If. As its MS SQL you are using you should have Books Online available which is the SQL server Help. Look up both Case and If. If you do not have access to Books Online on your machine then look it up on the Microsoft MSDN website.

bonkey

berengar wrote:

All i want to know is can I manipulate SQL select statements in MsSQL
(if statements etc) so I can split the phone number into prefixes etc.

Yes. You can.

I'm betting though that when you said that all you want to know is can you that you weren't being entirely accurate.....and you're also going to want to know how you can.

On that assumption.

Look up the functions that MSSQL supports for manipulating data.

Just as with almost any other programming language, you'll find your answer amongst such incredibly obscure functionality as SUBSTRING and so forth. Depending on what you erally need, you may also need to read up on CASE (as someone already suggested) but to be honest the original post is so unclearly worded I'm not sure.