HELP WITH 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

SQL Server 2000 Forums

SQL Server Development (2000)

HELP WITH SUBSTRING

Author

Topic

yaditzal
Starting Member

22 Posts

Posted - 2010-04-05 : 11:38:46

Hello.
please I need help with this:

table specialties

SPEC_DESC SPEC_CODE PRACTYPE
Optometrist OY 23-5
Gastroenterology GA 7-14
Endocrinology ED 1
Nephrology NP

The problem is I need to split the PRACTYPE in 2 records. it have to look like this:

SPEC_DESC SPEC_CODE PRACTYPE
Optometrist OY 23
Optometrist OY 5
Gastroenterology GA 7
Gastroenterology GA 14
Endocrinology ED 1
Nephrology NP

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 11:51:38

see

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=89817

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 15:38:49

Do you need to handle records like this:
Optometrist OY 23-5-42
Gastroenterology GA 7-14-6-21

There are 10 types of people in the world, those that understand binary, and those that don't.

yaditzal
Starting Member

22 Posts

Posted - 2010-04-05 : 16:27:34

YES THAT THE PROBLEM.
I HAVE A COLUMN WITH MORE THAN ONE VALUE FOR THE SPECIALTY TYPE AND I NEED TO SPLIT IN ANOTHER ROW FOR EACH SPECIALTY TYPE
LIKE THIS
Optometrist OY 23
Optometrist OY 5
Optometrist OY 42
Gastroenterology GA 7
Gastroenterology GA 14
Gastroenterology GA 21

-14-6-21

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 16:53:40

Assuming the tablename is Spec, this should do what you want.


DECLARE @Count INT SET @Count = 1

	WHILE @Count > 0
	BEGIN
		INSERT INTO Spec		
		SELECT SPEC_DESC, SPEC_CODE, 
			LEFT(PRACTYPE, CHARINDEX('-', PRACTYPE) - 1)
		FROM Spec
		WHERE PRACTYPE LIKE '%-%'

		SET @Count = @@ROWCOUNT
		IF @Count > 0
		BEGIN
			UPDATE Spec
			SET PRACTYPE = SUBSTRING(PRACTYPE, CHARINDEX('-', PRACTYPE) + 1, 8000)
			WHERE PRACTYPE LIKE '%-%'
		END
	END

	SELECT * FROM Spec

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 01:02:52

[code]
--sample data only for illustration
select * into #Tabl
FROM
(

SELECT 'Optometrist' AS SPEC_DESC, 'OY' AS SPEC_CODE, '23-5' AS PRACTYPE UNION ALL
SELECT 'Gastroenterology' ,'GA' ,'7-14-15-64-39' UNION ALL
SELECT 'Endocrinology', 'ED', '1-123-345-567-890' UNION ALL
SELECT 'Nephrology', 'NP',''

)t

--Your actual solution
SELECT t.SPEC_DESC,t.SPEC_CODE,
CASE
WHEN t.PRACTYPE <> ''
THEN SUBSTRING('-'+t.PRACTYPE,v.number+1,CHARINDEX('-','-'+t.PRACTYPE+'-',v.number+1)-v.number-1)
ELSE t.PRACTYPE
END
FROM #Tabl t
INNER JOIN master..spt_values v
ON v.type='p'
AND (v.number <=LEN(t.PRACTYPE) OR t.PRACTYPE = '')
AND SUBSTRING('-'+t.PRACTYPE,v.number,1)='-'

DROP TABLE #Tabl

output
-----------------------------
SPEC_DESC SPEC_CODE PRACTYPE
Optometrist OY 23
Optometrist OY 5
Gastroenterology GA 7
Gastroenterology GA 14
Gastroenterology GA 15
Gastroenterology GA 64
Gastroenterology GA 39
Endocrinology ED 1
Endocrinology ED 123
Endocrinology ED 345
Endocrinology ED 567
Endocrinology ED 890
Nephrology NP

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources