Get the few letter of word in SQL

user_9901032 0
Viewed 138

I am looking get few letters in a string of words. So it should be first 5 characters (if there are more than 5 if not then the whole word and the subsequent first characters of the next words 5+1(+1...)

E.g. Key Account Sales I would like to get just "KeyAS" or HR General - HRG

I am currently using this

ALTER FUNCTION [dbo].[fnFirsties5]
(
    @str NVARCHAR(4000)
)
RETURNS NVARCHAR(2000)
AS
BEGIN
    DECLARE retval NVARCHAR(2000);

    SET str=RTRIM(LTRIM(@str));
    SET retval=LEFT(@str,5);

    WHILE CHARINDEX(' ',@str,1) > 0 BEGIN
        SET str=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));
        SET retval+=LEFT(@str,1);
    END

    RETURN retval;
END –

but this gives a result as "Key AAS" or "HR GeG"

Few more things to note

Version - Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) (Hypervisor)

Currently what I get

Empl ID Name    Job Family           Description
123 John Smith  Key AAS              Key Account Sales
124 karen Smith HR GeG               HR General
213 John Doe    Production Operator  Production Operator

What I expect

Empl ID Name    Job Family       Description
123 John Smith  Key AS           Key Account Sales
124 karen Smith HR G             HR General
213 John Doe    ProduO           Production Operator

Code that I am currently using is

ALTER FUNCTION [dbo].[fnFirsties5] ( @str NVARCHAR(4000) )
RETURNS NVARCHAR(2000)
AS
BEGIN
    DECLARE @retval NVARCHAR(2000);

    SET @str=RTRIM(LTRIM(@str));
    SET @retval=LEFT(@str,5);

    WHILE CHARINDEX(' ',@str,1)>0 BEGIN
        SET @str=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));
        SET @retval+=LEFT(@str,1);
    END

    RETURN @retval;
END

Thanks

tsql sql-server sql
4 Answers

If I understand the requirements

Declare @YourTable table (SomeCol varchar(50))
Insert into @YourTable Values
 ('Key Account Sales')
,('HR General')
,('Customer Support Group')

Select SomeCol
      ,NewValue = concat( left(JSON_VALUE(S,'$[0]'),5)
                         ,left(JSON_VALUE(S,'$[1]'),1)
                         ,left(JSON_VALUE(S,'$[2]'),1)
                         ,left(JSON_VALUE(S,'$[3]'),1)
                         ,left(JSON_VALUE(S,'$[4]'),1)
                         ,left(JSON_VALUE(S,'$[5]'),1)
                         ,left(JSON_VALUE(S,'$[6]'),1) -- Expand if more than 7 words
                        )
  From @YourTable A
  Cross Apply ( values ( '["'+replace(string_escape(SomeCol,'json'),' ','","')+'"]' ) ) B(S)

Results

SomeCol                 NewValue
Key Account Sales       KeyAS
HR General              HRG
Customer Support Group  CustoSG

EDIT - If you'd rather a function

CREATE Function [dbo].[YourFunction] (@S varchar(150))
Returns varchar(50)
Begin
    Return (
            Select concat( left(JSON_VALUE(S,'$[0]'),5)
                          ,left(JSON_VALUE(S,'$[1]'),1)
                          ,left(JSON_VALUE(S,'$[2]'),1)
                          ,left(JSON_VALUE(S,'$[3]'),1)
                          ,left(JSON_VALUE(S,'$[4]'),1)
                          ,left(JSON_VALUE(S,'$[5]'),1)
                          ,left(JSON_VALUE(S,'$[6]'),1) -- Expand if more than 7 words
                           )
             From (values ( '["'+replace(string_escape(@S,'json'),' ','","')+'"]' ) ) A(S)
           )
End

Usage

Select [dbo].[YourFunction]('Key Account Sales')

EDIT--- 2008 XML Version

CREATE Function [dbo].[YourFunction] (@S varchar(150))
Returns varchar(50)
Begin
    Return (
            Select coalesce(xDim.value('/x[1]','varchar(5)'),'')
                  +coalesce(xDim.value('/x[2]','varchar(1)'),'')
                  +coalesce(xDim.value('/x[3]','varchar(1)'),'')
                  +coalesce(xDim.value('/x[4]','varchar(1)'),'')
                  +coalesce(xDim.value('/x[5]','varchar(1)'),'')
                  +coalesce(xDim.value('/x[6]','varchar(1)'),'')
                  +coalesce(xDim.value('/x[7]','varchar(1)'),'')
             From  ( values (cast('<x>' + replace((Select replace(@S,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml))) as A(xDim)
           )
End

Usage

Select [dbo].[YourFunction]('Key Account Sales')
user_15700000

You can mostly pull this off using STRING_SPLIT() functionality. This is sketchy though since STRING_SPLIT() doesn't output the ordinal of the term that is being split. That being said, we can sort-of fake it with CHARINDEX(). But realize that ordinal will fail in the event that the same word is repeated in your input string. This may still provide a path forward for you though:

CREATE TABLE t1 (s1 VARCHAR(500));
INSERT INTO t1 VALUES ('This Is A Test');
INSERT INTO t1 VALUES ('And Another Test');
INSERT INTO t1 VALUES ('Key Account Sales');

SELECT
  s1, 
  SUBSTRING(STRING_AGG(CASE WHEN rn = 1 THEN SUBSTRING(value, 1, 5) ELSE SUBSTRING(value, 1, 1) END, ''), 1, 5) as output
FROM
  (
    SELECT 
      s1,  
      value,
      ROW_NUMBER() OVER (PARTITION BY s1 ORDER BY CHARINDEX(value, s1, 1)) as rn  
    FROM t1
      CROSS APPLY STRING_SPLIT(s1, ' ')
  ) sub
GROUP BY s1

+-------------------+--------+
|        s1         | output |
+-------------------+--------+
| And Another Test  | AndAT  |
| Key Account Sales | KeyAS  |
| This Is A Test    | ThisI  |
+-------------------+--------+
user_22210010

One more very elastic approach running on v2008 (which you really should upgrade...)

--Some test values in a declared table variable

DECLARE @input TABLE(YourString VARCHAR(1000))
INSERT INTO @input VALUES('Key Account Sales')
                        ,('HR General')
                        ,('Production Operator')
                        ,('') --test edge cases
                        ,(NULL)
                        ,('a b c d e f g')
                        ,('Some incredibly long and stupid text to test even this');

--the query (borrowed the FROM(VALUES( from John's answer)

SELECT xDim.query(N'(
                     <itm>{substring(concat((/x[@pos=1]/text())[1]," "),1,5)}</itm>
                    ,for $rest in /x[empty(@pos)]
                     return
                        <itm>{substring(($rest/text())[1],1,1)}</itm>
                    )
                   ').value('.','nvarchar(max)')
FROM @input i
CROSS APPLY(VALUES(CAST('<x pos="1">' + REPLACE((SELECT REPLACE(i.YourString,' ','§§Split§§') AS [*] FOR XML PATH('')),'§§Split§§','</x><x>')+'</x>' AS XML))) as A(xDim);

--the result

Key AS
HR G
ProduO
 
 
a bcdefg
Some ilastttet

The idea in short

  • I use the same approach as John Cappelletti did (XML) to create a XML where each word is living in its own element.
  • Other than John, I add pos="1" to the first <x>
  • Now we can use XQuery
  • The first step is to pick 5 characters from the first word (attention: I add a blank before I cut 5)
  • XQuery-for will iterate through the rest (no @pos) and append the very first character.
  • The final .value will just get all data as a string.

Hint: upper-case() within XQuery helps to get the single characters "big"...

user_50892040

first create one split string function which will also help you in other requirement.

String Split Function

SELECT TOP (4000)
    number = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.tblNumber1
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2


CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.tblNumber1 (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

CREATE FUNCTION [dbo].[DelimitedSplitString] (
    @pString VARCHAR(8000)
    ,@pDelimiter CHAR(1)
    )
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN
WITH cteStart(N1) AS (
        SELECT 1

        UNION ALL

        SELECT t.number + 1
        FROM dbo.tblNumber1 t
        WHERE number < datalength(@pString)
            AND SUBSTRING(@pString, t.number, 1) = @pDelimiter
        )
    ,cteLen(N1, L1) AS (
        SELECT s.N1
            ,ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0) - s.N1, 8000)
        FROM cteStart s
        )

SELECT ItemNumber = ROW_NUMBER() OVER (
        ORDER BY l.N1
        )
    ,Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l;

Then example,

DECLARE @YourTable TABLE
(id      INT IDENTITY(1, 1) PRIMARY KEY, 
 SomeCol VARCHAR(50)
);
INSERT INTO @YourTable
VALUES('Key Account Sales'), ('HR General'), ('Customer Support Group'), ('Production Operator');
WITH CTE
     AS (SELECT *
         FROM @YourTable
              CROSS APPLY
         (
             SELECT ItemNumber, 
                    Item
             FROM [dbo].[DelimitedSplitString](SomeCol, ' ')
         ) ca),
     CTE1
     AS (SELECT *, 
         (
             SELECT LEFT(Item, 1) + ''
             FROM CTE c1
             WHERE c.id = c1.id
                   AND c1.ItemNumber > 1 FOR XML PATH('')
         ) AS [Rest]
         FROM CTE c
         WHERE itemnumber = 1)
     SELECT *,
            CASE
                WHEN LEN(SUBSTRING(item, 1, 5)) = 5
                THEN SUBSTRING(item, 1, 5)
                ELSE SUBSTRING(item, 1, 5) + ' ' + [Rest]
            END [Job Family]
     FROM cte1
     WHERE itemnumber = 1;
user_11111690
Related