Charindex/Substring - 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

Charindex/Substring

Author

Topic

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2009-07-02 : 02:30:00

Hi,

I want to DO the following:

COLUMN1
CAN10-12
CAN10-11
CAN100-16
CAN50-12
CAN5-10

The part starting from left(column1,3) up till the '-' (10,100,50,5)....If the string was the same length everytime, I would just youse substring,but it differs.Please advise how to do the Charindex(or what to use) to pull only the part from (column1,3) up till the '-'

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 02:34:18

select charindex('-',column1,1),left(column1,charindex('-',column1,1)) as firstval
from urtablename

charindex(symbol,columnname,startloc)
see charindex,substring in booksonline

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2009-07-02 : 03:00:13

Please advise more on the above plz

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 03:09:12

[code]
if can maintain constant then try this
select left(col1,charindex('-',col1,1)-1)as firstpart
,substring(col1,4,(charindex('-',col1,1)-1)-3)as middlepart,right(col1,charindex('-',reverse(col1),1)-1) as lastpart
from (
select 'CAN10-12' as col1 union all
select 'CAN10-11' union all
select 'CAN100-16')s
[/code]

Rheinhardt
Yak Posting Veteran

66 Posts

Posted - 2009-07-02 : 03:17:59

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:22:55

or for varying length of string


select original_col,parsename(col1,2) as milddle,parsename(col1,1) as last from
(
	select col1 as original_col,replace(substring(col1,patindex('%[0-9]%',col1),len(col1)),'-','.') as col1	from 
	(
		select 'CAN10-12' as col1 union all 
		select 'CAN10-11' union all 
		select 'CAN100-16'
	) as s
) as t

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-02 : 03:43:58

quote:
Originally posted by Rheinhardt

Thanks

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources