0

I've been working on a rather convoluted process to format some data for a work project. We received a data extract and need to export it for import during a migration, but some of the data won't import due to case sensitivity (user logons with sentence case for example).

In an ideal world, I could demand the data be sanitised and formatted before it's provided for me to build the import, but I can't, so I'm stuck where I have to format it myself.

Plan:

  • Take string result
  • Split string result by pipe delimitation
  • Format each split results ( ) into lower case (where applicable)
  • Put all split results back into one string using FOR XML PATH

Example of problem:

Field 'Assigned To' can contain a pipe delimitted string of users and/or user groups, e.g.

John Smith (jsmith)|College Group of Arts|Bob Jones (BJones)

Now as you can see above, John Smith (jsmith) looks fine, as does College Group of Arts, however Bob Jones has had his logon sentence cased, so I need to use a LOWER command, chained with SUBSTRING and CHARINDEX to convert the logon to lower. Standalone, this approach works fine, but the problem I'm having is where I'm using a function found here on Stack Overflow (slightly manipulated to account for pipe delimitation) T-SQL split string.

When I retrieve the table results of the split string, I can't apply CHARINDEX against any characters in the result string, and I can't work out why.

Scenario:

The raw data extract, untouched, returns the below when queried;

|College of Science Administrators|Bob Jones (BJones)|

I then apply the below query, which calls the function queried above;

declare @assignedto nvarchar(max) = (select assigned_to from project where project_id = 1234)

SELECT SUBSTRING(Name,CHARINDEX(Name,'('),255)

FROM dbo.splitstring(@assignedto)

I then get the below results;

College of Science Administrators
Bob Jones (BJones)

What I'd expect to see is;

College of Science Administrators
(BJones)

I could then apply my LOWER logic to change it to lower case.

If that worked, then thought process was then to take those results and pass them back into a single string using a FOR XML PATH.

So I guess technically, there are 2 questions here;

  • Why won't my function let me manipulate the results with CHARINDEX?
  • And is this the best way to do what I'm trying to achieve overall?
halfer
  • 19,824
  • 17
  • 99
  • 186
mrc85
  • 73
  • 10

2 Answers2

1

I would strongly suggest you take that splitstring function you found and throw it away. It is horribly inefficient and doesn't even take the delimiter as a parameter. There are so many better splitter options available. One such example is the DelimitedSplit8K_LEAD which can be found here.

I noticed you also have your delimiters at the beginning and the end so you have to eliminate those but not a big deal. Here is how I would go about parsing this string. I am using a variable for your string here with the value you said is in your table.

declare @Something varchar(100) = '|College of Science Administrators|Bob Jones (BJones)|'

select MyOutput = case when charindex('(', x.Item) > 1 then substring(x.Item, charindex('(', x.Item), len(x.Item)) else Item end
from dbo.DelimitedSplit8K_LEAD(@Something, '|') x
where x.Item > ''
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Cheers, I'll give that a whirl. It's the second organisation I've worked for where they don't use SQL Server 2017, so I've not been able to utilise the STRING_SPLIT function which would make my life so much easier! – mrc85 Nov 21 '19 at 10:19
0

For question #1 you must simply invert parameters in CharIndex : 

CHARINDEX('(', Name))
EddiGordo
  • 682
  • 4
  • 10