Substring & Charindex - 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 2000 Forums

SQL Server Development (2000)

Substring & Charindex

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-20 : 07:29:59

Hi guys,
I have 2 queries.
1) I have a table with a column which contains data like this
102.130.456.868
What I want is I want to seggregate the data into diff columns I mean 102 in col1,130 in col2 & so on.
I used this query
select substring(Numbers,1,charindex('.',Numbers)-1) from table but the problem is it gives the position of the first dot only.
How can I find the position of the other consecutive dots.

The second problem is that
Sometimes the data can come even without any dots i.e the column can contain data lk this
178
So if i again run the above query I get an error.
'Invalid length parameter passed to the substring function.'
because it cannot find the dot in the given data.So how can I handle that error

Thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-06-20 : 08:01:01

One simple way using Sql's built-in function PARSENAME. but that assumes your values have are no more than 4 parts. If your values can contain more that 4 parts then I sugggest you search this site for "parsing". There are a lot of topics


select	parsename(nm,1)
	,parsename(nm,2)
	,parsename(nm,3)
	,parsename(nm,4)
from	
	(select '102.130.456.868' nm
	union select '178') a

output:
868	456	130	102
178	NULL	NULL	NULL

Be One with the Optimizer
TG

ayamas
Aged Yak Warrior

552 Posts

Posted - 2006-06-20 : 08:34:48

Thanks TG.
It worked perfectly.
Once again thank you very much..!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 09:01:02

If you use front end application, then split data there. If you have more than four parts you need to use differenct approach

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources