sql quirey please help - 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 quirey please help

Author

Topic

mohan.pati
Starting Member

7 Posts

Posted - 2009-02-19 : 06:18:32

In My table (CLI) two columns are htere
1)Ani 2) DESTcode

those destinct ani Depends upon DESTCODE

for example for One cli=4444444

the output is like this

4444444 PAKISTAN KARACHI
4444444 SAUDI ARABIA MOBILE
4444444 SPAIN MOBILE
4444444 UNITED ARAB EMIRATES PROPER
4444444 PAKISTAN MOBILE
4444444 UNITED ARAB EMIRATES MOBILE

Pakistan IS displaying twice like pkistan karachi and pakistan mobile

my intensions is it shold displyes single time only for each

there is no ther primary key to filter please help

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-02-19 : 06:30:44

How do you distinguish? UAE shows twice as well according to your logic?!?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 06:37:11

you mean PAKISTAN KARACHI' and 'PAKISTAN MOBILE' are the same and should display as 'PAKISTAN'?
Wouldn't you want to apply the same logic to 'UNITED ARAB EMIRATES PROPER' and 'UNITED ARAB EMIRATES MOBILE'??

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 06:38:04

quote:
Originally posted by RickD

How do you distinguish? UAE shows twice as well according to your logic?!?

sorry.. didn't see your post. I meant to ask the same question.

mohan.pati
Starting Member

7 Posts

Posted - 2009-02-19 : 06:58:34

at least is it possible to get first word Before space.

mohan.pati
Starting Member

7 Posts

Posted - 2009-02-19 : 07:04:34

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 07:06:02

select ani,left(destcode,charindex(' ',destcode,1)) from tablename

mohan.pati
Starting Member

7 Posts

Posted - 2009-02-19 : 07:25:17

If i need two words how it possibble

mohan.pati
Starting Member

7 Posts

Posted - 2009-02-19 : 07:32:18

if i need to two words in the perticluar line how it posssible

srihari nandamuri
Starting Member

6 Posts

Posted - 2009-02-19 : 09:18:27

Hi
Use the below select to get two words in ur destcode column

select distinct ani,left(destcode,charindex(' ',destcode,charindex(' ',destcode,1)+1 )) from tablename

srihari nandamuri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:21:35

quote:
Originally posted by mohan.pati

if i need to two words in the perticluar line how it posssible

please explain what you want by means of some sample output. what will your desired output out of above data?

srihari nandamuri
Starting Member

6 Posts

Posted - 2009-02-19 : 09:26:18

Hi Visakh16
i hope i answered his query above.he needs like if a name contains "abc defff ghiaaa" ,then he needs the name as "abc defff".

quote:
Originally posted by visakh16

quote:
Originally posted by mohan.pati

if i need to two words in the perticluar line how it posssible

please explain what you want by means of some sample output. what will your desired output out of above data?

srihari nandamuri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 09:34:09

quote:
Originally posted by srihari nandamuri

Hi
Use the below select to get two words in ur destcode column

select distinct ani,left(destcode,charindex(' ',destcode,charindex(' ',destcode,1)+1 )) from tablename

srihari nandamuri

will this work for cases where you've only two words? i dont think it will

srihari nandamuri
Starting Member

6 Posts

Posted - 2009-02-19 : 10:26:09

Hi Visakh,

Below query which will work for all cases.
select distinct ani,left(destcode,charindex(' ',destcode+' ',charindex(' ',destcode,1)+1 )) from tablename.

It will be helpful if u can give solutions instead of questions/queries.

quote:
Originally posted by visakh16

quote:
Originally posted by srihari nandamuri

Hi
Use the below select to get two words in ur destcode column

select distinct ani,left(destcode,charindex(' ',destcode,charindex(' ',destcode,1)+1 )) from tablename

srihari nandamuri

will this work for cases where you've only two words? i dont think it will

srihari nandamuri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 11:22:39

quote:
Originally posted by srihari nandamuri

Hi Visakh,

Below query which will work for all cases.
select distinct ani,left(destcode,charindex(' ',destcode+' ',charindex(' ',destcode,1)+1 )) from tablename.

It will be helpful if u can give solutions instead of questions/queries.

quote:
Originally posted by visakh16

quote:
Originally posted by srihari nandamuri

Hi
Use the below select to get two words in ur destcode column

select distinct ani,left(destcode,charindex(' ',destcode,charindex(' ',destcode,1)+1 )) from tablename

srihari nandamuri

will this work for cases where you've only two words? i dont think it will

srihari nandamuri

but wasnt i right?


SELECT DISTINCT ani,
CASE WHEN LEN(DESTcode)-LEN(REPLACE(DESTcode,' ',''))>1
     THEN STUFF(DESTcode,LEN(DESTcode)-CHARINDEX(' ',REVERSE(DESTcode)),CHARINDEX(' ',REVERSE(DESTcode)),'')
FROM Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources