| Author |
Topic |
|
jodders
Starting Member
41 Posts |
 Posted - 2012-02-07 : 10:28:09
|
Hello there,I have a column which consists of text mainly by a user. Examples of the data looks like this:Yes - Great jobNo - Uh-ohRecent - No ChangeDanger - Needs attentionIs it possible to split the cell into two columns, so that one column before the - sign will show the statuses and the other column will show the narrative? Before, I was thinking use the LEFT column but because the length of the text is variable, it will not work :(LEFT (TT7.TXT1,1) Any kind folks point me in the right direction? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-07 : 10:33:04
|
try left() in combination with charindex()
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-07 : 10:35:14
|
You can use STUFF like this, but if there are no dashes, results are sort of crappy. (If you don't believe me, remove the dash in this example and try)DECLARE @x VARCHAR(32);SET @x = 'Danger - Needs attention';SELECT STUFF(@x,1,CHARINDEX('-',@x)+1,''), STUFF(@x,CHARINDEX('-',@x),LEN(@x),''); |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-07 : 10:41:54
|
| I think you'd be better splitting on the literal ' - ' (that is <space><dash><space>)'Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
jodders
Starting Member
41 Posts |
Posted - 2012-02-07 : 11:33:42
|
Guys thank you so much for your responses, as always this forum fails not to deliver to us unexperience folk.Decided to go with WebFred's suggestion of combining the left with Charindex which delivered exactly what I needed.SunitaBeck and Charlie, thanks for the example using the Declare function, that will come in handy in the future.i thought using the right charindex after the "-" will get the text afterwards but does not work at all. It just cuts off randomly. hm!(right(TT7.TXT1,charindex('- ',TT7.TXT1))) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-07 : 12:53:03
|
For the right part use substring() and charindex() for offset and len(column) for length Posted from my smartphone...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jodders
Starting Member
41 Posts |
Posted - 2012-02-07 : 12:54:43
|
| substring(TT7.TXT1,charindex('-',TT7.TXT1) +1,LEN(TT7.TXT1))Just figured it WebFred, thanks again. :) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-07 : 12:57:24
|
Are you sure there is always the needed "-"-sign in your data?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Splitting a string function?