分割字符串并显示第一个字符

发布于 2025-01-17 22:33:09 字数 208 浏览 0 评论 0原文

我需要分解姓名字段以在单独的字段中显示姓氏和名字首字母。

到目前为止，我可以分解姓氏、名字，但是有没有办法只能选择名字首字母？

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自找没趣 2025-01-24 22:33:09

我很困惑没有看到“ Oracle”标签。因此，这些代码片段仅适用于SQL Server：

您可以使用标准字符串功能来执行此操作。

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

回报：

名字
Sheldon T.

和

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

Lastname	FirstName
Sheldon	T

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

回复收藏 0 原文

失退 2025-01-24 22:33:09

您可以将 SUBSTR 与 regexp_substr 的结果一起使用

其中 rws 为 (
  从 Dual 中选择 'lastname,firstname' str
）
 选择 regexp_substr(str, '[^,]+', 1, 1) 作为姓氏，
 
     SUBSTR(regexp_substr(str, '[^,]+', 1, 2),1,1) 作为 FirstNameinitial
  来自 RWS

姓氏 |名字首字母
:----- | :----------------
姓氏 | f

db<>fiddle 此处

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

回复收藏 0 原文

香橙ぽ 2025-01-24 22:33:09

哈！

这可能不太雅观，但它有效：

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

我从 2 开始子字符串，b/c 逗号和名字之间有一个空格。我现在有两列：LastName、FirstInit

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

回复收藏 0 原文

爱情眠于流年 2025-01-24 22:33:09

假设分隔符是逗号，并且使用老式instr（） and substr（），此版本将允许可能存在零或更多空间跟随逗号：

WITH nrow AS (
    SELECT 'Lastname, Firstname' AS pers_name
    FROM   dual
)
    SELECT SUBSTR (pers_name, 1, INSTR (pers_name, ',') - 1) AS last_name,
           SUBSTR (LTRIM (SUBSTR (pers_name, INSTR (pers_name, ',') + 1)), 1, 1) AS first_init
    FROM   nrow
/

Assuming that the separator is a comma, and using old-school INSTR() and SUBSTR(), this version will allow for the possibility that there may be zero or more spaces following the comma:

WITH nrow AS (
    SELECT 'Lastname, Firstname' AS pers_name
    FROM   dual
)
    SELECT SUBSTR (pers_name, 1, INSTR (pers_name, ',') - 1) AS last_name,
           SUBSTR (LTRIM (SUBSTR (pers_name, INSTR (pers_name, ',') + 1)), 1, 1) AS first_init
    FROM   nrow
/

回复收藏 0 原文

~没有更多了~