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.
| Author |
Topic |
|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2013-11-01 : 15:14:34
|
| In an SSRS 2008 existing report, I have one column that I need to separate the values. I am not allowed to change the database. I am told that I need to work with the data as it is.The values in the column look like the following099 11-12 Midwest Plumbers.I need to split the data to look like1. 099 is the Customer Number.2. 11-12 is the year the customer data was valid.3. Midwest Plumbers is the name of the company.Notes:1. The delimiter between the 3 fields is " " (one space).2. The company name can contain lots of spaces.3. There are 3 fields that need to be separated out which are:a. Customer Number,b. Effective Years,c. Customer Name.I 'best' solution, I have so far is:=Mid(Parameters!pCust.Value,InStr(Parameters!pCust.Value," ") + 1,Len(Parameters!pCust.Value)-Instr(Parameters!pCust.Value," ")).However the above only gets me the first value.Thus can you show me how to split up the in this column within SSRS? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-01 : 15:36:54
|
| You should be able to use the Split function (to get the first and second pieces). To get the third piece use JOIN (to join the first two pieces) and then REPLACE to replace the joined string from the full string.I am not at a computer that has Visual Studio installed to test it out. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-02 : 13:59:07
|
| I would prefer doing this in T-SQLIf you want to do it in SSRS you can use like this1. to get Customer Number=Split(Parameters!pCust.Value," ").GetValue(0)2. to get Effective Years=Split(Parameters!pCust.Value," ").GetValue(1)3. To get Company Name=Replace(Replace(Parameters!pCust.Value,Split(Parameters!pCust.Value," ").GetValue(0),""),Split(Parameters!pCust.Value," ").GetValue(1),"")I would have done this simply in t-sql using string parsing udf or by using PARSENAME function and brought them as individual values in SSRS------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jassie
Constraint Violating Yak Guru
332 Posts |
Posted - 2013-11-03 : 01:20:24
|
| Thanks! |
|
|
|
|
|
|
|
|
SSRS 2008 r2 substring