Choose desired condition - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Choose desired condition

Author

Topic

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 00:05:06

I'm using SQL Server 2000. I had data that looks like this

1234, successful
1234, invalid message
4444, winner of prize
4444, successful
4444, invalid format
6666, hello
6666, bulk sms
1111, invalid msg
1111, invalid format

i need to display only the data with 'successful'. And the data that don't have 'successful', can choose either one of it.. So the output will look like this.

1234, successful
4444, successful
6666, hello <--- can choose either one (hello or bulk sms)
1111, invalid format <--- can choose either one

Can someone please help me :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 00:21:58

[code]DECLARE @TABLE TABLE
(
code int,
data varchar(30)
)
INSERT INTO @TABLE
SELECT 1234, 'successful' UNION ALL
SELECT 1234, 'invalid message' UNION ALL
SELECT 4444, 'winner of prize' UNION ALL
SELECT 4444, 'successful' UNION ALL
SELECT 4444, 'invalid format' UNION ALL
SELECT 6666, 'hello' UNION ALL
SELECT 6666, 'bulk sms' UNION ALL
SELECT 1111, 'invalid msg' UNION ALL
SELECT 1111, 'invalid format'

SELECT code,
data = CASE WHEN SUM(CASE WHEN data = 'successful' THEN 1 ELSE 0 END) > 0
THEN 'successful'
ELSE MAX(data)
END
FROM @TABLE
GROUP BY code

/*
code data
----------- ------------------------------
1111 invalid msg
1234 successful
4444 successful
6666 hello
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 02:27:01

Wow..thank you.. it works perfectly.. Thanks khtan!

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 03:23:02

erm..i need another help...

my current data looks like this.

60193060957
146024632
0169272494
60123625334
60122869889
60124701486
168232465

i want to insert 60 in the front of each data.. How am i to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 03:25:25

[code]
update t
set col = '60' + col
from yourtable t
[/code]

Why don't you store the country code in separate column ?

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 03:36:15

Opsss.. when i tried

update telephone
set phoneno = '60' + phoneno
from telephone

the output looks like this

6060123456789
6060123456789
60123456789
60123456789
60123456789

i dont want to modify all the data..just the data that dont have 60 in front of them..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 03:45:19

[code]UPDATE t
SET phoneno = '60' + phoneno
FROM telephone t
WHERE phoneno NOT LIKE '60%'[/code]

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 04:04:35

thanks..it works... but one problem though

i have data that looks like this

0123456789

when i execute the above query

it becomes

600123456789

how to elimate the second or third zero (0)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 04:07:17

[code]UPDATE t
SET phoneno = CASE WHEN left(phoneno, 1) = '0'
THEN '6'
ELSE '60'
END + phoneno
FROM telephone t
WHERE phoneno NOT LIKE '60%'[/code]

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 04:10:14

erm..rite now i already execute it..
so i had thousands of data that have double zero in it

600123456789
600987654321

if i use the above query, will it eliminate the zero?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 04:13:42

quote:
Originally posted by massspectrometry

erm..rite now i already execute it..
so i had thousands of data that have double zero in it

600123456789
600987654321

if i use the above query, will it eliminate the zero?

Nope.

use this

UPDATE t
SET    phoneno = REPLACE(phoneno, '600', '60')
FROM   telephone t
WHERE  phoneno LIKE '600%'

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 04:19:07

Wow..u really amazing in sql... If i know you personally, i would buy u a lunch.. Hehe.. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 04:33:04

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 22:05:01

hi.. i have some query that i want to solve. i had a data that looks like this. (it's actually a text messages where people sms in to win prizes) and it have to start with 'PRM'

SMSReceived <-- column name
Prm 65719471
Prm 68516237
PRM 72847410
Prm 75031193
PRM (SPACE) PINSEND TO32080
prm (space)36398226
PRM < > 22733564
Prm < 82848916 > pin
PRM <36943554>

how can i choose only the number? so the desired output will look like this..
65719471
68516237
72847410
75031193
32080
36398226
22733564
82848916
36943554

please help..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 23:01:39

search the script library in this forum. There is a script that does this.

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 23:12:23

i find two in the script library which is topic 'number pattern' and sum the cubes of numeric... can u give me a lead pls :(

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-11 : 23:28:32

i can't find it... please help :((

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 00:01:05

try http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33433

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-12 : 00:12:50

(feel really stupid)
how to use it? i run that script in my query analyzer and it said success.. then ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 00:16:19

That is a user defined function. What you did is to create the function.
See this line ?

quote:
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')

Use it as follows


select dbo.fn_extract_chars(SMSReceived, 'numbers')
from   yourtable

KH
[spoiler]Time is always against us[/spoiler]

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-07-12 : 00:25:10

can we choose multiple column using that above query? lets say i have this query..

SELECT dnyFrom as 'SMS From',
max(dnySMSText) as 'SMS Received',
max(dnyCustomerId) as 'Customer ID',
dnyReason = CASE WHEN SUM(CASE WHEN dnyReason = 'Successful' THEN 1 ELSE 0 END) > 0
THEN 'successful'
ELSE MAX(dnyReason)
END
FROM shldnysmsreceived
where dnycustomerid='PRM' and
dnyDateReceived between '2006-12-01 00:00:00.000' and '2007-07-01 00:00:00.000'
GROUP BY dnyFrom
order by max(dnySmsText) asc

and i want also to display another column with only numbers (like above) query.. can it be done?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources