SQL select questuion - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL select questuion

Author

Topic

berengar
Starting Member

21 Posts

Posted - 2006-06-06 : 05:22:38

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

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-06 : 05:34:48

What have you tried so far?

-------
Moo. :)

berengar
Starting Member

21 Posts

Posted - 2006-06-06 : 05:39:34

if tried if statements within the SELECT statment,
like

SELECT ct_surname 'Surname',ct_name 'First Name',ct_address 'Address',ct_zip 'Zip Code',
ct_city 'Town',
if (ct_phone_number like '4')
{ select ct_phone_number 'MOBILE'}

else
{
select ct_phone_number 'LandLine')
}

FROM easy.ct_vitalis
WHERE ct_city like 'Helsinki'
----------
But with this i get access violation errors ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 05:47:49

can you post some sample data and the result that you want ?

berengar
Starting Member

21 Posts

Posted - 2006-06-06 : 05:56:38

Well for example in the table phone numbers are stored under ct_phone_number field,
I want to select the phone numbers and store them in a field called 'phone number' and if have the prefixes in brackets, and if its a mobile
(starting with 4 in finland) just the mobile number with no prefix
like below
Phone: 141234567 -> Fix line -> (14) 1234567
Phone: 401234567 -> Mobile -> () 401234567

Maybe a new colomn aswell with the prefix, so this would be NULL
with a mobile number and 14 or whatever with a landline.
I just dont see why I cant get a simple if statment to work in MSSQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 05:59:44

You can't use IF ELSE in a select statement. You have to use CASE WHEN ... THEN ... END

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-06 : 06:02:39

You don't need curly brackets. Try replacing them with BEGIN and END. In fact that whole bit of IF could be

SELECT ct_surname 'Surname',ct_name 'First Name',ct_address 'Address',ct_zip 'Zip Code',
ct_city 'Town',
CASE WHEN ct_phone_number like '4%' then ct_phone_number ELSE NULL END AS 'MOBILE',
CASE WHEN ct_phone_number not like '4%' then ct_phone_number ELSE NULL END AS 'LANDLINE'

-------
Moo. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 06:02:57

Something like this

select	Phone, 
	case 	when left(Phone, 1) = '4' then
			'()' + Phone
		else
			'(' + left(Phone, 2) + ')' + right(Phone, len(Phone) - 2)
		end
from    yourtable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources