Multiple Mid functions

Question

Multiple Mid functions

mack53nico

SSC-Addicted

Points: 451
More actions
November 24, 2017 at 6:45 am

#324858

Hi,
Apologies is this query is too specific for this forum, please point me in the right direction if that's the case.
Below is an expression I am using to pull out the forename from a user ID i.e."text\[forename] [Surname]", at the moment this expression is suitable if " " is between the forename and surname. However, what i have noticed is that " " can be replaced by "." or "/" or "_" for example.
The trouble I am having is including multiple "Mid" functions to include all these options. I have attempted using "Or", "Switch" and a number of other operators with no effect.
Am I missing something obvious or have i missed the mark completely?
Any help would be greatly aprpreciated.
= "Hi " + Mid(Parameters!User.Value, InStr(Parameters!User.Value,"\")+1, (InStr(Parameters!User.Value, " ") - InStr(Parameters!User.Value, "\") - 1)) + "," &
chr(10) & chr(13) &
chr(10) & chr(13) &
"You have access to the Teams listed below. Only data from these teams will be shown in any analysis. Please click above to continue to the Dashboards"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Do you have to do this with an expression? I ask, as it may be easier to return the parts in your dataset, rather than splitting them in the report. For example:

WITH Users AS(
  SELECT *
   FROM (VALUES ('sabdbsdlkfbaskl f\Steve Jobs'),
 ('asdkbgaksdaskbdk\Jane-Smith'),
 ('kasjdgbasdvajklsvd\Craig.Charles'),
 ('sdfgasdjlfgalsdhfsajldf\Jennifer_Lopez'),
 ('sdiafgaskjdkjhobkjah\Bill/Gates')) V (UserID)),
Names AS(
   SELECT U.UserID,
     RIGHT(U.UserID, CHARINDEX('\',REVERSE(U.UserID))-1) AS FullName
   FROM Users U)
SELECT *,
    LEFT(FullName, PATINDEX('%[/ \._-]%',FullName)-1) AS FirstName,
    RIGHT(FullName, PATINDEX('%[/ \._-]%',REVERSE(FullName))-1) AS Surname
FROM Names N;

You don't need to do with a CTE, but just easier to see what it's doing, IMO.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mack53nico SSC-Addicted Points: 451 More actions · Answer 2

Thank you for your reposnse, however my question is parameter based i.e. i don't actually have/need ( i've simply inputted SELECT 'fred' to avoid any errors).

The UserID as a parameter is generated when a user enters the SSRS report and depending on their permissions, they will recieve the message "You have access.....". It's just that the UserID can include a / . _