MID, CHARINDEX OR 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

MID, CHARINDEX OR SUBSTRING??

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2011-03-30 : 05:47:00

Hi

Simple one this, for people in the know. I simply don’t know the terminology of what I’m looking for here.

I would like to split the file in table below by the ':' symbol.

What is the syntax for getting everything LEFT and everything RIGHT of the ':' symbol into a seperate field.

For rowkey 1 I would like to be able to see ARMPMAEWC in 1 field and ARMPM in another.

Thanks

SAMPLE DATA

CREATE TABLE PS_TestForOnline
(
rowkey INT,
CLINIC VARCHAR (25),

)

INSERT INTO PS_TestForOnline
VALUES('1','ARMPMAEWC:ARMPM' );
INSERT INTO PS_TestForOnline
VALUES('2','ACPAED:AC');
INSERT INTO PS_TestForOnline
VALUES('3','RCELDC:RC');
INSERT INTO PS_TestForOnline
VALUES('4','LEEDMECCH:LEED');

select * from PS_TestForOnline

Paul

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 06:13:09

I like table variables... so I changed you're DDL

Several ways to do this... if its just a single delimiter, this is an easy approach.


Declare @PS_TestForOnline TABLE 
(
rowkey INT,
CLINIC VARCHAR (25)
)

INSERT INTO @PS_TestForOnline
VALUES('1','ARMPMAEWC:ARMPM' );
INSERT INTO @PS_TestForOnline
VALUES('2','ACPAED:AC');
INSERT INTO @PS_TestForOnline
VALUES('3','RCELDC:RC');
INSERT INTO @PS_TestForOnline
VALUES('4','LEEDMECCH:LEED');



select 
	*,
	SUBSTRING(clinic,1,charindex(':',clinic,1)-1),
	reverse(SUBSTRING(reverse(clinic),1,charindex(':',reverse(clinic),1)-1))
from @PS_TestForOnline

Corey

I Has Returned!!

sqlnovice1
Starting Member

34 Posts

Posted - 2011-03-30 : 08:14:47

many thanks, works a treat.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-30 : 08:34:02

If there realyy is just one ':'

select rowkey,clinic
,parsename(replace(clinic,':','.'),1)
,parsename(replace(clinic,':','.'),2)
from @PS_TestForOnline

Jim

Everyday I learn something that somebody else already knew

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 08:52:37

Oh yeah.. i sorgot about parsename

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources