InsideDarkWeb.com

sql query for select first word of data from column

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

Database Administrators Asked on November 11, 2021

3 Answers

3 Answers

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

Answered by Doug B on November 11, 2021

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]

Answered by a_horse_with_no_name on November 11, 2021

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.

Answered by Dominique Boucher on November 11, 2021

Add your own answers!

Related Questions

SSRS Standalone install license

1  Asked on July 22, 2020 by preben-huybrechts

     

Ask a Question

Get help from others!

© 2021 InsideDarkWeb.com. All rights reserved.