sql 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

SQL Server 2005 Forums

Transact-SQL (2005)

sql help

Author

Topic

new_user
Starting Member

3 Posts

Posted - 2007-04-26 : 09:58:56

I have column that has records like

1.2.1
1.2.2
1.2.1.1
1.2.1.1.1
1.2.9
1.2.9.3

i need to display only 1.2.1 , 1.2.2 ,1.2.9 as the others contain one of these.

I am able to do this in java. I want to do this using sql statement. Is this possible? appreciate ur reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 10:07:15

something like this ?


declare @table table
(
	col	varchar(10)
)

insert into @table
select	'1.2.1'		union all
select	'1.2.2'		union all
select	'1.2.1.1'	union all
select	'1.2.1.1.1'	union all
select	'1.2.9'		union all
select	'1.2.9.3'

select	a.col
from	@table a inner join @table b
on	a.col	like b.col + '%'
group by a.col
having count(*) = 1

/* result :
col        
---------- 
1.2.1
1.2.2
1.2.9
*/

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-26 : 10:07:23

Try this:

SELECT A.*
FROM YourTable A INNER JOIN YourTable B
ON B.YourColumn LIKE A.YourColumn + '%' AND
A.YourColumn != B.YourColumn

SQL Server Helper
http://www.sql-server-helper.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 11:21:20

Much simpler:

declare @table table
(
	col	varchar(10)
)

insert into @table
select	'1.2.1'		union all
select	'1.2.2'		union all
select	'1.2.1.1'	union all
select	'1.2.1.1.1'	union all
select	'1.2.9'		union all
select	'1.2.9.3'

select	col
from	@table
where	col like '[0-9].[0-9].[0-9]'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 11:26:37

Harsh,

What if there is a record like '1.1' ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 11:35:15

Well, in that case I have to modify my solution to take of that as well.

declare @table table
(
	col	varchar(10)
)

insert into @table
select	'1.2.1' union all
select	'1.2.2' union all
select	'1.2.1.1' union all
select	'1.2.1.1.1' union all
select	'1.2.9' union all
select	'1.2.9.3' union all
select	'1.2' union all
select	'1'

select	col
from	@table
where	
	col like '[0-9].[0-9].[0-9]' 	-- x.x.x
	or col like '[0-9].[0-9]' 	-- x.x
	or col like '[0-9]'		-- x

One other situation where this solution won't work is when there are more than one digit in each section like, 11.274.42

But, let the OP clearly specify if those conditions are part of his requirements. As a general rule, whenever, the solutions are made more generic, they are more complex and bit costly.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

new_user
Starting Member

3 Posts

Posted - 2007-04-26 : 15:27:31

thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-26 : 15:43:08

select distinct left(col,5)
from yourtable

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 16:44:25

select col
from table1
where datalength(col) - datalength(replace(col, '.', '')) = 2

Peter Larsson
Helsingborg, Sweden