split 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

SSIS and Import/Export (2005)

split string

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-17 : 04:38:19

Hi,

How can i split strings such as 1235-5tgy6 into 2 columns(before and after the -)?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-17 : 04:48:18

Here's one option:
In the Data Flow Transformation, use a Derived Column Transformation: http://msdn.microsoft.com/en-us/library/ms141069(SQL.90).aspx
by using the expression
SUBSTRING(<StringColumn>, 1 ,FINDSTRING((<StringColumn> ,"-" ,1))

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-20 : 04:48:36

Thanks. I managed to get the string before the - but not after it. Can someone please help?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-20 : 06:51:17

Try this:
RIGHT(<StringColumn>, FINDSTRING((<StringColumn> ,"-" ,1) + 1) )

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-20 : 10:38:24

Thanks. This is what i came up with and it seems to be working fine.
RIGHT([Material Number],FINDSTRING(REVERSE([Material Number]),"-",1) - 1)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-20 : 11:38:15

Try this too:
select parsename(replace('1235-5tgy6','-','.'),2)
select parsename(replace('1235-5tgy6','-','.'),1)

No, you're never too old to Yak'n'Roll if you're too young to die.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-21 : 08:11:52

quote:
Originally posted by webfred

Try this too:
select parsename(replace('1235-5tgy6','-','.'),2)
select parsename(replace('1235-5tgy6','-','.'),1)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred, how can I use the PARSENAME function in an SSIS expression?
Do I have to register/reference an additional resource?

These are the only functions I can use in an expression: http://msdn.microsoft.com/en-us/library/ms141671(SQL.90).aspx
And would like to use the PARSENAME and LEFT function from SQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 03:42:25

quote:
Originally posted by webfred

Try this too:
select parsename(replace('1235-5tgy6','-','.'),2)
select parsename(replace('1235-5tgy6','-','.'),1)

No, you're never too old to Yak'n'Roll if you're too young to die.

This will only work in T-SQL and not in expression filed SSIS or SSRS

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-22 : 03:52:16

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

Try this too:
select parsename(replace('1235-5tgy6','-','.'),2)
select parsename(replace('1235-5tgy6','-','.'),1)

No, you're never too old to Yak'n'Roll if you're too young to die.

This will only work in T-SQL and not in expression filed SSIS or SSRS

Madhivanan

Failing to plan is Planning to fail

Yes - my bad.
I've to clean my glasses

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 03:57:46

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

Try this too:
select parsename(replace('1235-5tgy6','-','.'),2)
select parsename(replace('1235-5tgy6','-','.'),1)

No, you're never too old to Yak'n'Roll if you're too young to die.

This will only work in T-SQL and not in expression filed SSIS or SSRS

Madhivanan

Failing to plan is Planning to fail

Yes - my bad.
I've to clean my glasses

No, you're never too old to Yak'n'Roll if you're too young to die.

Thats why you should always see the forum name

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-22 : 14:40:36

Thanks for the help

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources