Substring Function - 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 Function

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-08-08 : 13:37:45

Guys,
I have column in the following format which I am trying to parse it to 3 different columns, but substring in SQL Server does not seem to give me enough options to do so

Field1 Field2 Field3 Field4
____________________________________________
AB343/293 AB 343 293
AB 009/240 AB 009 240

Also first 2 charecters are always AB so substring works to parse it out, however the numbers on the either of '/' are not being handled by the substring

Any ideas???

Thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-08 : 13:46:48

SELECT LTRIM(SUBSTRING(field1, 3, CHARINDEX('/', field1) -3) as Field3
SELECT RIGHT(field1, LEN(field1) - CHARINDEX('/', field1)) as Field4

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-08-08 : 17:25:25

Thanks, that works just fine dude

Part of the same example (AB233/345) I should eliminate all fields with more than one '/'

i.e. I should not parse field1 such as (AB233/1/23) is it possible to include a clause which counts the no of '/', I was not able to do it through charindex with combination of count() function for this

Any ideas/suggestions

Thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-08 : 17:58:39

WHERE field1 NOT LIKE '%/%/%'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-08 : 19:12:15

This will count the number of occurances of / in a string:

select
	String,
	-- Length of string minus length after /'s removed
	[No of /'s] = len(a.String)-len(replace(a.String,'/',''))
from
	(
	-- Sample data
	select String = 'AB233/1/230'	union all
	select String = 'AB233/1///230'	union all
	select String = 'AB233/1/230/'	union all
	select String = 'AB2331230'	
	) a

quote:
Originally posted by scelamko

Thanks, that works just fine dude

Part of the same example (AB233/345) I should eliminate all fields with more than one '/'

i.e. I should not parse field1 such as (AB233/1/23) is it possible to include a clause which counts the no of '/', I was not able to do it through charindex with combination of count() function for this

Any ideas/suggestions

Thanks

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources