Extracting first characters in a string

Question

Extracting first characters in a string

sathishkm

SSC Veteran

Points: 239
More actions
October 8, 2022 at 3:38 am

#4096846

Hi All,
How could I can extract all the first characters in the string using SQL Server query

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

If you have Jeff Moden's infamous dbo.DelimitedSplit8K splitter function installed (link), this becomes fairly straightforward:

DROP TABLE IF EXISTS #SomeText;

CREATE TABLE #SomeText
(
    Name VARCHAR(500) NOT NULL
);

INSERT #SomeText
(
    Name
)
VALUES
('Sathish Kumar')
,('A B C')
,('Shijay')
,('Some other random stuff');

SELECT st.Name
      ,InitialLetters = STRING_AGG(spl.InitialLetter, ' ') WITHIN GROUP(ORDER BY spl.ItemNumber)
FROM #SomeText st
    CROSS APPLY
(
    SELECT ItemNumber
          ,InitialLetter = LEFT(Item, 1)
    FROM dbo.DelimitedSplit8K(st.Name, ' ')
) spl
GROUP BY st.Name;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 2

sathishkm wrote:

Hi All,
How could I can extract all the first characters in the string using SQL Server query

Just a posting tip... if you post "Readily Consumable Data" in the future instead of a picture, people will respond to your post a lot quicker. You were lucky that Phil saw this one (he's a kind soul 😀 ) and posted working code that also demonstrates how to post "Readily Consumable Data".

For a bit more on why you should take the time, please read the article at the first link in my signature line below. It also shows one of a few ways to generate such data from a table without much strain or pain on your part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

As a bit of a sidebar, you may have some people come back with a suggestion of using the built-in STRING_SPLIT() function. That will seem to work but there is absolutely no guarantee that it will produce the output in the correct order until SQL Server 2022 comes out, where the finally realized that you need a position ordinal for each value returned in order to guarantee a correct sort.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 4

Jeff Moden wrote:

As a bit of a sidebar, you may have some people come back with a suggestion of using the built-in STRING_SPLIT() function. That will seem to work but there is absolutely no guarantee that it will produce the output in the correct order until SQL Server 2022 comes out, where the finally realized that you need a position ordinal for each value returned in order to guarantee a correct sort.

You could use this one instead https://www.sqlservercentral.com/scripts/a-varcharmax-string_split-function-for-sql-2012-and-above

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

Jonathan AC Roberts wrote:

Jeff Moden wrote:
As a bit of a sidebar, you may have some people come back with a suggestion of using the built-in STRING_SPLIT() function. That will seem to work but there is absolutely no guarantee that it will produce the output in the correct order until SQL Server 2022 comes out, where the finally realized that you need a position ordinal for each value returned in order to guarantee a correct sort.
You could use this one instead https://www.sqlservercentral.com/scripts/a-varcharmax-string_split-function-for-sql-2012-and-above

One of these days, you'll need to post a performance test for that, Jonathan. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)