Simple Select query problem - 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

Simple Select query problem

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-19 : 01:44:44

One my column containt data like 'digit-digit-digit' (i.e. '45-25-65')
I want to select all records where MyColumn NOT LIKE myformate

Sample Data:

Valid:
'45-25-65'
'1-02-53'
'568-1-522'
INVALID:
'-25-65'
'--65'
'1--1-0'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 01:49:06

can u post some sample output

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-19 : 01:51:22

Sample Data for Mycolumn:
'45-25-65'
'1-02-53'
'568-1-522'
'-25-65'
'--65'
'1--1-0'

Out of these six rows only first three should return.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-19 : 02:01:16

declare @temp table ( Valid varchar(32))
insert into @temp
select '45-25-65' union all
select '1-02-53' union all
select '568-1-522' union all
select '-25-65' union all
select '--65' union all
select '1--1-0'

select * from @temp where valid like '%[1-9]%-%[1-9]%-%[1-9]%'

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-19 : 02:05:47

quote:
Originally posted by Nageswar9

declare @temp table ( Valid varchar(32))
insert into @temp
select '45-25-65' union all
select '1-02-53' union all
select '568-0-522' union all
select '-25-65' union all
select '--65' union all
select '1--1-3'

select * from @temp where valid like '%[1-9]%-%[1-9]%-%[1-9]%'

THIS WONT WORKS FOR THE ABOVE VALUES. CHECK IT OUT

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-19 : 02:14:08

May be this works in all cases just check this out.

declare @t table (a varchar(50))

insert into @t select '45-25-65' UNION ALL
select'1-02-53' UNION ALL
select'568-0-522' UNION ALL
select'-25-65' UNION ALL
select'--65' UNION ALL
select'1--1-3'

select a from @t where charindex('-',a,1)<>1
and charindex('-',reverse(a),1)<>1
and charindex('--',a,1) = 0

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-01-19 : 02:22:33

hi
is that works for '56-8-0-522' ???

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-19 : 02:24:29

Try this

Assuming that ur field contains only 3 parts

declare @table table (Valid varchar(888))
insert into @table
select '45-25-65' union all
select '1-02-53' union all
select '568-1-522' union all
select '-25-65' union all
select '--65' union all
select '1--1-0'

select * from @table where datalength(valid)-datalength(replace(valid,'-',''))=2 and valid like '[^-]%' and valid like '%[^-]'
and valid not like '%--%'
Jai Krishna

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-19 : 02:28:39

quote:
Originally posted by soorajtnpki

hi
is that works for '56-8-0-522' ???

Digit-Digit-Digit is the requirement

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 02:32:31

hai soorajtnpki,
karthik code will work for this also '56-8-0-522'
check once

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-01-19 : 03:23:38

no,

karthik's query is returning '56-8-0-522' in result.this shouldnt happen..

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-01-19 : 03:35:11

try this pls
select col from table_name where charindex('-',col,1) > 0
and charindex('-',col,charindex('-',col,1)+1)>0
and charindex('-',col,charindex('-',col,charindex('-',col,1)+1)+1) =0
and col like '%[1-9]%-%[1-9]%-%[1-9]%'

ok tanx...

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-20 : 05:03:53

Thanx soorajtnpki,
solution is bit lengthy but it works...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources