Efficient way to parse string - 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 2005 Forums

Transact-SQL (2005)

Efficient way to parse string

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2010-06-23 : 07:14:01

I am having a column in table that has space separated values. I need to get the value from first space till the second space. For example,
in "Division A District 10", i need "A" as it's after first space and before second space in string.

Below is the sample data and query i am using to find this:

Declare @StringTable table
(
colvalue varchar(50)
)
INSERT INTO @StringTable

SELECT 'Division A District 10' UNION ALL
SELECT 'Division B District 10' UNION ALL
SELECT 'Division C District 10' UNION ALL
SELECT 'Division 0D District 10'
SELECT rtrim(ltrim(substring(SUBSTRING(colvalue, patindex('% %' , colvalue), LEN(colvalue)),1,patindex('% %' , rtrim(ltrim(SUBSTRING(colvalue, patindex('% %' , colvalue), LEN(colvalue)))))))) AS DIVISION FROM @StringTable

Is there any efficient way to achieve this?

Thanks,

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-23 : 07:44:21

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Search for "Split" functions....when your data is delimtied by "spaces"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-23 : 08:07:45

As there are only four parts to the string you could also try PARSENAME:


SELECT PARSENAME(REPLACE(colvalue, ' ', '.'), 3)
FROM @StringTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources