Split string and display first character-CodePudding

I need to break out the name field to show last name and first initial in separate fields.

So far, I can break out the LastName, FirstName, but is there a way I can only select the first name initial?

     regexp_substr(name, '[^,] ', 1, 1) as LastName,
     regexp_substr(name, '[^,] ', 1, 2) as FirstName

CodePudding user response：

I'm confused didn't see "oracle" tag. So these code snippets will only work with SQL Server:

You can do this by using standard string functions.

DECLARE @name VARCHAR(100);
SELECT @name = 'Tom Sheldon'

-- you can use this instead of regular expressions
SELECT RIGHT(@name, CHARINDEX(' ', @name) 3)   ' '   LEFT(@name, 1)   '.' AS name

Returns:

name
Sheldon T.

and

DECLARE @name VARCHAR(100);
SELECT @name = 'Tom Sheldon'

-- you can use this instead of regular expressions
SELECT
    RIGHT(@name, CHARINDEX(' ', @name) 3) AS LastName,
    LEFT(@name, 1) AS FirstName

Returns:

LastName	FirstName
Sheldon	T

CodePudding user response：

you can use SUBSTR with the resuklt of the regexp_substr

with rws as (
  select 'lastname,firstname' str from dual
)
 SELECT     regexp_substr(str, '[^,] ', 1, 1) as LastName,
 
     SUBSTR(regexp_substr(str, '[^,] ', 1, 2),1,1)  as FirstNameinitial
  from rws

LASTNAME | FIRSTNAMEINITIAL
:------- | :---------------
lastname | f

db<>fiddle here

CodePudding user response：

Hah!

This might be inelegant, but it worked:

substr ( (regexp_substr(name, '[^,] ', 1, 2)),2,1 ) as FirstName

I started the substring at 2, b/c there was a space between the comma and the first name. I now have two columns: LastName, FirstInit