-2

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
2
  • 1
    Postgres or SQL Server? Those are two very different database products. Jul 21 '20 at 18:34
  • yes there are three 3 different rows in a single column...how to select and show only first word of each name?
    – shubham
    Jul 21 '20 at 19:01
1

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.

3
  • 1
    Make sure to add a trailing space to handle names like Prince and Madonna. Jul 21 '20 at 18:27
  • but I have to select data from table
    – shubham
    Jul 21 '20 at 19:02
  • 1
    @shubham As the answer explains, replace @name with the column name from your table (you didn't tell us the name of the table or the column). Also, can you please update the question to make it clear whether you are using PostgreSQL or Microsoft SQL Server. Jul 23 '20 at 16:05
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

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
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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