Get the few letter of word in SQL-CodePudding

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 currenlty 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

CodePudding user response：

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  |
 ------------------- --------

CodePudding user response：

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')