CODEWITHSUNDEEP
sql-servert-sql
J - C Sharper
J - C Sharper

Reputation: 1697

How to get the abbreviation of a word in TSQL?

For example, if I have an input string "OrderTrackingNumber", I want it to return "OTN". Or if I have an input string "MyOrder", I want it to return MO.

Upvotes: 2

Views: 1886

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Starting with SQL Server 2017 you should read about the new function TRANSLATE. Together with a case sensitive collation this should be good for your issue.

But most people don't have this version (me too). So you might try this:

DECLARE @mockupTable TABLE(ID INT IDENTITY, InputString VARCHAR(100));
INSERT INTO @mockupTable VALUES('OrderTrackingNumber')
                              ,('MyOrder')
                              ,('OneMoreExample');

WITH recCTE AS
(
    SELECT ID
          ,InputString
          ,2 AS NextPos
          ,SUBSTRING(InputString,1,1) AS Letter
    FROM @mockupTable AS mt

    UNION ALL
    SELECT r.ID
          ,r.InputString
          ,r.NextPos+1
          ,SUBSTRING(r.InputString,r.NextPos,1)
    FROM recCTE AS r
    WHERE r.NextPos<LEN(r.InputString)+1
)   
SELECT r1.ID
      ,(
        SELECT r2.Letter AS [*]
        FROM recCTE AS r2
        WHERE ASCII(r2.Letter) BETWEEN ASCII('A') AND ASCII('Z')
          AND r1.ID=r2.ID
        FOR XML PATH('')
       )
FROM recCTE AS r1
GROUP BY r1.ID

The recursive CTE will walk along the letters and return them one by one.
The final query uses a grouping query outside and a sub-select, returned as XML.
This will be the re-concatenated string.
Without the inner WHERE checking for the ASCII() values the result would be exactly the same as the initial values.

Upvotes: 0

John Bell
John Bell

Reputation: 2350

Providing your column names are always camel cased with the first letter capitalised, you can use the following:

create function fn_extractupper(@var varchar(50))
returns varchar(50)
as
begin

declare @aux varchar(50) = ''
declare @size int = len(@var)
declare @position int = 0
while @position < @size
    begin
        if ASCII(SUBSTRING(@var,@position,1)) = ASCII(UPPER(SUBSTRING(@var,@position,1)))
            begin
                set @aux = @aux + SUBSTRING(@var,@position,1)
            end
        set @position = @position + 1
    end

return @aux
END

Upvotes: 1

Related Questions