SQL SERVER 2008 split by forward slash

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

SQL SERVER 2008 split by forward slash

Author

Topic

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-05 : 22:49:07

I am trying to split a string by forward slash "/". Any assistant or direction is greatley appreciated.


CREATE TABLE #MyTable
   (  
      OrgTree VARCHAR(50)
     
    )
 
-- Load Sample Data
INSERT INTO #MyTable VALUES ( 'AMM/SOUTH/NORTH/004/12345' )
INSERT INTO #MyTable VALUES ( '121/2003/444/333/12346' )
INSERT INTO #MyTable VALUES ( '101/2022-1/444/333/12347')
INSERT INTO #MyTable VALUES ( '108/20-33/874/333/12349' )

Here is the desire output:

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-05 : 22:52:51

[code]
right(OrgTree, charindex('/', reverse(OrgTree)) - 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-05 : 23:14:09

khtan - i should have been clear, there could be more forward slash in the right. So I need start the count from from the left.
'121/2003/444/333/12346////'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-05 : 23:20:30

quote:
Originally posted by emyk

khtan - i should have been clear, there could be more forward slash in the right. So I need start the count from from the left.
'121/2003/444/333/12346////'

so what do you want to return for the above ?

or do you always wants the 5th element ?

KH
[spoiler]Time is always against us[/spoiler]

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-05 : 23:22:08

yes, I want to return the 5th from the left.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-05 : 23:34:21

use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-07 : 17:26:35

I review the suggested link where I have to use a split function, the only issue I have with that is I don't have access to create function on the client DB.

But I want to make additional clarification with the type of data I am dealing with is that the targeted data starts always with a number and always the first number occurrence in a given string. Using the first number occurrence character as a starting position and to display the preceding 5 characters.

Example:


AMM/SOUTH/NORTH/PMSOUTH/12345//
AM-EAST/SOUTH/PM/PMSOUTH/1ABCD////

Desired outcome:
12345
1ABCD

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-07 : 18:04:30

quote:
Originally posted by emyk

I review the suggested link where I have to use a split function, the only issue I have with that is I don't have access to create function on the client DB.

But I want to make additional clarification with the type of data I am dealing with is that the targeted data starts always with a number and always the first number occurrence in a given string. Using the first number occurrence character as a starting position and to display the preceding 5 characters.

Example:

AMM/SOUTH/NORTH/PMSOUTH/12345//
AM-EAST/SOUTH/PM/PMSOUTH/1ABCD////

Desired outcome:
12345
1ABCD

But your original sample data does not follow that pattern. Do all the rows follow this new pattern, or is it a mix? If all the rows follow this rule, then you can use the following:

LEFT(STUFF( OrgTree,1,PATINDEX('%/[0-9]%',OrgTree),''),5)

emyk
Yak Posting Veteran

57 Posts

Posted - 2013-06-07 : 18:22:26

James - All rows follow this new pattern and your solution works perfect!

thank you so much!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-07 : 18:30:02

You are very welcome! Glad it worked out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources