check for EMAIL format in ROW and return value inside SELECT query

I have a table called user_data and few columns like LOGIN_NAME

The column LOGIN_NAME may contain “[e-mail]” or just a “username”

I need to know if it is possible for me to run a select query and return emailAddress if row has email type format else return userName if it is a non-email format.

example – (I am trying to created a JSON format with this) :

Here signInType is userName since Markus Dave is a non-email format (LOGIN_NAME which is a row was having a value Markus Dave ->

        {
          "signInType": "userName",
          "issuerAssignedId": "Markus Dave" 
        }

Here signInType is emailAddress since [e-mail] is a email format (LOGIN_NAME which is a row was having a value [e-mail]

            {
              "signInType": "emailAddress",
              "issuerAssignedId": "[e-mail]" 
            }
      

Here is a query that I could produce :

SELECT 'signInType' AS signInType
        , LOGIN_NAME AS issuerAssignedId
        FROM USER_DATA        
        FOR JSON AUTO;

For signInType field, I need to use the same field LOGIN_NAME and check if it is email or not and then prepare according to it.

I have 10 users in the table, I need to create 10 field JSON.

Answer

You should be able to use a case expression:

SELECT (CASE WHEN LOGIN_NAME LIKE '%@%' THEN 'emailAddress' ELSE 'userName' END) as signInType,
       LOGIN_NAME AS issuerAssignedId
FROM USER_DATA        
FOR JSON AUTO;