CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Christophe Debove
Christophe Debove

Reputation: 6296

How to obtain a string equals to each first letter of words in a sentence IN SQL

I have words separated with a space in a column like apple orange banana I need the first letters as the result will be something like :

aob

Upvotes: 0

Views: 1686

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option using a little XML. You could also use ParseName() provided you trap any periods in the string.

Example

Declare @YourTable table(ID int,LastName varchar(50),FirstName varchar(50))
Insert Into @YourTable values
(1,'Waston','Mary Jane')

Select A.ID
      ,NewValue = upper(
                      concat(
                            xmlData.value('/x[1]','varchar(1)')
                           ,xmlData.value('/x[2]','varchar(1)')
                           ,xmlData.value('/x[3]','varchar(1)')
                           ,xmlData.value('/x[4]','varchar(1)')
                           ,'.'
                           ,LastName
                       )
                   )
 From  @YourTable A
 Cross Apply ( values (convert(xml,'<x>' + replace(A.FirstName,' ','</x><x>')+'</x>' )) ) B(xmlData)

Returns

ID  NewValue
1   MJ.WASTON

EDIT - Added ParseName() option

Select A.ID
      ,NewValue = upper(concat(Pos1,Pos2,Pos3,Pos4,'.',LastName))
 From  @YourTable A
 Cross Apply (
                Select Pos1 = left(parsename(tStr,4),1)
                      ,Pos2 = left(parsename(tStr,3),1)
                      ,Pos3 = left(parsename(tStr,2),1)
                      ,Pos4 = left(parsename(tStr,1),1)
                 From  ( values(replace(FirstName,' ','.'))) B1(tStr)
             ) B

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I think the shortest will be this:

Here a mockup-table with two rows to simulate your issue:

DECLARE @mockup TABLE(ID INT IDENTITY,YourWords VARCHAR(100));
INSERT INTO @mockup VALUES('apple orange banana'),('one two three');

--That is the query:

SELECT m.ID
      ,REPLACE(Casted.query('for $w in /x return substring($w,1,1)').value('.','varchar(max)'),' ','')
FROM @mockup m
CROSS APPLY(SELECT CAST('<x>' + REPLACE(m.YourWords,' ','</x><x>') + '</x>' AS XML)) A(Casted);

The idea behind:
The string apple orange banana is tranformed to <x>apple</x><x>orange</x><x>banana</x> and is casted to XML, which allows to use XQuery.
Now we use .query() on the XML with a simple FLWOR statement. It tells the engine: run through each value of /x and return just the first letter. Calling value() on this with a . as XPath will return the values in one.
We need a final REPLACE() to get rid of blanks, which would otherwise appear as a o b instead of aob.

Upvotes: 0

aaDev
aaDev

Reputation: 143

If you declare REGEX function in your DB (not native with SQL SERVER).

Using regexp_replace

select regexp_replace('apple orange banana','(\\w)(\\w* ?)','$1')

return

aob

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

First, split your text. I recommend some function:

CREATE FUNCTION Split(@text nvarchar(MAX),@separator nvarchar(MAX))
RETURNS TABLE AS RETURN
WITH Indexed AS
(
    SELECT 1 N, CAST(1 AS bigint) S, CHARINDEX(@separator, @text, 1) E WHERE @text IS NOT NULL
    UNION ALL
    SELECT N+1, E+DATALENGTH(@separator)/2, CHARINDEX(@separator, @text, E+DATALENGTH(@separator)/2) FROM Indexed WHERE E>S
), Token AS
(
    SELECT N, SUBSTRING(@text, S, CASE WHEN E=0 THEN DATALENGTH(@text)/2 ELSE E-S END) T FROM Indexed
)
SELECT * FROM Token

If you are using SQL 2016 and greater, use STRING_SPLIT instead.

Then, you can select first character of every word and join. See following example:

DECLARE @Sample TABLE (T nvarchar(100));
INSERT @Sample VALUES (N'apple orange banana'),(N'dog cat');

SELECT (SELECT SUBSTRING(T,1,1) [*] FROM Split(T,N' ') FOR XML PATH(''))
FROM @Sample

Result:

(no column name)
------
aob
dc

Upvotes: 1

Related Questions