0

I have a table with this data:

shyam banarjee
rahul khanna
priya kumari 
rohit srivastava

I just want first names, like this:

shyam
rahul
priya
rohit
1
  • 1
    Postgres or SQL Server? Those are two very different database products.
    – user1822
    Jul 21, 2020 at 18:34

4 Answers 4

2

You can use "Substrig" and "charindex" functions to get what you want.

See this code as a demo on how to use it:

declare @name varchar(30)
set @name = 'shyam banarjee'
select SUBSTRING(@name,1,charindex(' ',@name)-1)

In your case, instead of being a variable in the select, it will be your column name.

You could also look at "computed column" if ever you want to have a new column that would only contains the firstname.

1
  • 1
    Make sure to add a trailing space to handle names like Prince and Madonna. Jul 21, 2020 at 18:27
1

This will allow you to choose which part by simply filtering on RowNum. It will work no matter how many values you have...(assuming data is in a table called #temp)

SELECT 
FIELD1,Value,ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELD1) RowNum
FROM 
#Temp T
CROSS APPLY STRING_SPLIT(FIELD1, ' ') T1

Specifically, this query will give you the first names. If you want last name, change it to RowNum=2 on last line.

SELECT Value FROM
(
SELECT 
        FIELD1,
        Value,
        ROW_NUMBER() OVER(PARTITION BY FIELD1 ORDER BY FIELD1) RowNum
    FROM 
        #Temp T
        CROSS APPLY STRING_SPLIT(FIELD1, ' ') T1
)
a 
WHERE RowNum=1

STRING_SPLIT documentation

0
0

In Postgres you can use split_part()

split_part(the_column, ' ', 1);

Or, if you need to deal with multiple spaces, you can use regexp_split_to_array()

(regexp_split_to_array(the_column, '\s+'))[1]
0
select substr(columnname,1,instr(columnname,' ')) from tablename;
1
  • Hi Alina, its best to explain the answer, so that it is obvious to all future visitors. Sep 3, 2023 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.