I have a table that includes names and allows for a "nickname" for each name in parenthesis.

I need to extract the Nickname, and return a column of nicknames and a column of full names (Full string without the nickname portion in parenthesis). I also need a condition for the nickname to be null if no nickname exists, and so that the nickname only returns letters. So far I have been able to figure out how to get the nickname out using Substring, but I can't figure out how to create a separate column for just the name.

Select SUBSTRING(PersonName, CHARINDEX('(', PersonName)  1,(((LEN(PersonName))-CHARINDEX(')',REVERSE(PersonName)))-CHARINDEX('(',PersonName))) 

as NickName from dbo.Person

Any help would be appreciated. I'm using MS SQL Server 2019. I'm pretty new at this, as you can tell.

CodePudding user response：

Using your existing substring, one simple way is to use apply.

Assuming your last row is an example of a nickname that should be NULL, you can use an inline if to check its length - presumably a nickname must be longer than 1 character? Adjust this logic as required.

select PersonName, Iif(Len(nn)<2,null,nn) NickName, Trim(Replace(Replace(personName, Concat('(',nn,')') ,''),'  ','')) FullName
from Person
cross apply (values(SUBSTRING(PersonName, CHARINDEX('(', PersonName)  1,(((LEN(PersonName))-CHARINDEX(')',REVERSE(PersonName)))-CHARINDEX('(',PersonName))) ))c(nn)

CodePudding user response：

The following code will deal correctly with missing parenthesis or empty strings.

Note how the first CROSS APPLY feeds into the next

SELECT
  PersonName,
  NULLIF(NickName, ''),
  FullName = ISNULL(REPLACE(personName, ' ('   NickName   ')', ''), PersonName)
FROM t
CROSS APPLY (VALUES(
  NULLIF(CHARINDEX('(', PersonName), 0))
) v1(opening)
CROSS APPLY (VALUES(
  SUBSTRING(
    PersonName,
    v1.opening   1,
    NULLIF(CHARINDEX(')', PersonName, v1.opening), 0) - v1.opening - 1
   )
)) v2(NickName);

db<>fiddleenter link description here