Extract substring due to lack of Split function

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)

Extract substring due to lack of Split function

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-15 : 08:55:40

I am unable to locate a Split function, and need to extract a substring from a string field of the format xxxxxx-yy where the x portion and y portion can be variable length. If a Split function were available it would use the delimiter '-'.

How can I extract the x and y portions from the string please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:59:09

[code]SELECT PARSENAME(REPLACE(yourstrringcol,'-','.'),2) AS firstpart,PARSENAME(REPLACE(yourstrringcol,'-','.'),1) AS secondpart FROM table[/code]

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

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 09:05:34

SPLIT function will give you two ROWS. If you want two COLUMNS then do as Visakh suggests, or you can use this (no idea which is more efficient)


SELECT
  [COLUMN_1] = substring(YourStrringCol + '-', 0 + 1, charindex('-', YourStrringCol + '-', 0 + 1) - 0 - 1 )
, [COLUMN_2] = substring(YourStrringCol + '-',  charindex('-', YourStrringCol + '-') + 1, 
               charindex('-', YourStrringCol + '-',  charindex('-', YourStrringCol + '-') + 1) 
                 - charindex('-', YourStrringCol + '-') - 1 )
FROM	dbo.MyTable

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-15 : 09:11:22

Both are interesting. many thanks for the replies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 09:13:15

or this too


SELECT t.yourstringcol,
MAX(CASE WHEN f.ID = 1 THEN f.Val ELSE NULL END) AS FirstPart, 
MAX(CASE WHEN f.ID = 2 THEN f.Val ELSE NULL END) AS SecondPart 
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.yourstringcol,'-') f
GROUP BY t.yourstringcol


parsevalues can be found in below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources