number field query - 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

Old Forums

CLOSED - General SQL Server

number field query

Author

Topic

David Adedeji
Starting Member

14 Posts

Posted - 2006-02-03 : 14:23:41

I am trying to develop this qyery that search the a number field that has an hyphen in the third and sixth position in the number field.

for example

id
123456789
123456789
123456789
12-34-4587
45-87-8596

results
12-34-4587
45-87-4587

Any ideas
Thanks for all your help and consideration.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-02-03 : 14:51:48

What ???

Are you trying to get the position of the field in question ?

you should not try to determine the "position" naturally because the DBMS will shift records around as data fragments over time. Add a column to your table called "tableID" (int) and auto increment it.

you can then search out the columns that have "-" with the % wildcards e.g

select * from your_table where column_name LIKE '%-%-%'

Is this what you want ???

Afrika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-03 : 14:59:15

CharIndex does the trick

declare @s varchar(20)

set @s = '123456789'
print charindex('-', @s) 

set @s = '12-34-4587'
print charindex('-', @s)

Select urfld from urtbl where charindex('-', urfld) = 3 or charindex('-', urfld) = 6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 04:08:52

select id from yourTable where substring(data,3,1)='-' and substring(data,6,1)='-'

Srinika, your query will return if id has hypen in either 3rd or 6th position

Madhivanan

Failing to plan is Planning to fail

David Adedeji
Starting Member

14 Posts

Posted - 2006-03-07 : 12:49:06

I still need help

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-07 : 12:54:26

Madhivanan gave you an answer that worked. What do you need help with?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-08 : 00:53:54

quote:
Originally posted by David Adedeji

I still need help

Did you get error?
Did you get unwanted result?
Did you try the query I suggested?

Madhivanan

Failing to plan is Planning to fail

David Adedeji
Starting Member

14 Posts

Posted - 2006-04-12 : 11:11:26

Please I need help

David Adedeji
Starting Member

14 Posts

Posted - 2006-04-12 : 11:12:25

madhivanan

I tried the query but no results were derived

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-12 : 11:18:31

Or if it's always in the 3rd and 6th position:


select * from your_table where column_name LIKE '__-__-%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-12 : 11:29:58

quote:
Originally posted by David Adedeji

madhivanan

I tried the query but no results were derived

It means you have no such data

Refer this

declare @t table(num varchar(20))
insert into @t
	select '123456789' as data union all
	select '123456789' union all
	select '123456789' union all
	select '12-34-4587' union all
	select '45-87-8596'

select num from @t
where substring(num,3,1)='-' and substring(num,6,1)='-'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources