Help please - 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 2005 Forums

Transact-SQL (2005)

Help please

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-17 : 08:56:37

Hi,
Thanks for that reply, i have executed using ur solution but it trims the next word also actually i don't want that.

for eg
the actual column value is
Grand xx
Grand DTO
Grand PBT
Celio
4Runner ltd
4Runner sd
Avalon XL
Corolla Deluxe
Mark
Extra long Double

after using ur solution the result is

Grand
Celio
4Runner
Avalon
Corolla
Mark
Extra

wat i need is?

if the column contains the values as
Grand xx
Grand DTO
Grand PBT
Celio
4Runner ltd
4Runner sd
Avalon XL
Corolla Deluxe
Mark
Extra long Double

i like to get the result as

Grand
Celio
4Runner
Avalon XL
Corolla Deluxe
Mark
Extra long Double

wat to do?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 08:58:37

so not just the first word then ?

by what logic do you include

Avalon XL
Corolla Deluxe
Extra Long Double

in your result set?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 08:58:44

How do you decide to get the whole string or only the 1st word ?

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 08:59:22

continued from...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106799

Em

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-17 : 09:08:40

quote:
Originally posted by elancaster

so not just the first word then ?

by what logic do you include

Avalon XL
Corolla Deluxe
Extra Long Double

in your result set?

Em

Hi,

Avalon XL ,Corollo deluxe,Grand xx it's all model name.
so, if it finds the same model name like Grand xx, Grand TT it should display it as Grand.
that's it.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 09:09:43

so... only strip it back to 1st word if it appears more than once in the table?

Em

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-17 : 09:20:20

quote:
Originally posted by elancaster

so... only strip it back to 1st word if it appears more than once in the table?

Em

s s s

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 09:33:17

quote:
Originally posted by vidhya

s s s

what do you mean by s s s ? ? ?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 09:35:52

quote:
Originally posted by vidhya

quote:
Originally posted by elancaster

so not just the first word then ?

by what logic do you include

Avalon XL
Corolla Deluxe
Extra Long Double

in your result set?

Em

Hi,

Avalon XL ,Corollo deluxe,Grand xx it's all model name.
so, if it finds the same model name like Grand xx, Grand TT it should display it as Grand.
that's it.

You should use two columns Grand and model to avoid any confusions

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 09:36:30

quote:
Originally posted by khtan

quote:
Originally posted by vidhya

s s s

what do you mean by s s s ? ? ?

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

Chat language

yes yes yes

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 09:37:11

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

quote:
Originally posted by vidhya

s s s

what do you mean by s s s ? ? ?

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

Chat language

yes yes yes

Madhivanan

Failing to plan is Planning to fail

i feel so old

vidhya
Posting Yak Master

108 Posts

Posted - 2008-07-17 : 09:37:33

Actually, i replied for that before ans that's y i said it.
For my query

If the 1st word of the column value appears more than once then it should be removed.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 09:46:38

quote:
Originally posted by elancaster

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

quote:
Originally posted by vidhya

s s s

what do you mean by s s s ? ? ?

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

Chat language

yes yes yes

Madhivanan

Failing to plan is Planning to fail

i feel so old

Em

me too

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 09:47:23

quote:
Originally posted by vidhya

Actually, i replied for that before ans that's y i said it.
For my query

If the 1st word of the column value appears more than once then it should be removed.

declare @t table(data varchar(100))
insert into @t
select 'Grand xx' as data union all
select 'Grand DTO' as data union all
select 'Grand PBT' as data union all
select 'Corolla Deluxe' as data union all
select 'Mark' union all
select '4Runner ltd'

select distinct case when t2.counting>1 then t2.data else t1.data end as data from @t t1
inner join
(
	select rtrim(left(data,charindex(' ',data + ' ',1))) as data, count(*) as counting
	from @t group by rtrim(left(data,charindex(' ',data + ' ',1))) 
) as t2 on t1.data like t2.data+'%'

However consider my suggestion of using two different columns

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 09:55:04

[code]DECLARE @TABLE TABLE
(
col varchar(20)
)
INSERT INTO @TABLE
SELECT 'Grand xx' UNION ALL
SELECT 'Grand DTO' UNION ALL
SELECT 'Grand PBT' UNION ALL
SELECT 'Celio' UNION ALL
SELECT '4Runner ltd' UNION ALL
SELECT '4Runner sd' UNION ALL
SELECT 'Avalon XL' UNION ALL
SELECT 'Corolla Deluxe' UNION ALL
SELECT 'Mark' UNION ALL
SELECT 'Extra long Double'

;WITH yak(col, word)
AS
(
SELECT col,
word = CASE WHEN CHARINDEX(' ', col) <> 0
THEN left(col, CHARINDEX(' ', col) - 1)
ELSE col
END
FROM @TABLE
)
SELECT word = coalesce(y.col, d.word)
FROM (
SELECT word, cnt = COUNT(*)
FROM yak
GROUP BY word
) d
left JOIN yak y ON y.word = d.word
AND d.cnt = 1
ORDER BY word

/*
word
--------------------
4Runner
Avalon XL
Celio
Corolla Deluxe
Extra long Double
Grand
Mark

(7 row(s) affected)
*/[/code]

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 09:56:28

prob not the best but my version too...


declare @t table (col varchar(30))
insert into @t
select 'Grand xx' 
union all select 'Grand DTO'
union all select 'Grand PBT'
union all select 'Celio'
union all select '4Runner ltd'
union all select '4Runner sd'
union all select 'Avalon XL'
union all select 'Corolla Deluxe'
union all select 'Mark'
union all select 'Extra long Double'


select distinct case when left(Col,charindex(' ',Col+' ',1)) IN (select left(Col,charindex(' ',Col+' ',1))
							from @t 
							group by left(Col,charindex(' ',Col+' ',1))
							having COUNT(*) =1)
			then COL 
			else left(Col,charindex(' ',Col+' ',1))
			end
from @t

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 09:57:51

yeah . . i am getting old

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources