none
find first character from each word

    Question

  • Hi,

    Can someone help me out with writing a stored procedure which will loop round a string, take the first letter, look for the next space, take the next letter.. repeat, the output being an abbreviated string.

    So far I've got:

    loop round the string. Take the first letter, do an instr for the next space, take the next letter.. repeat. The output is your abbreviated string.
    CREATE PROCEDURE [dbo].[usp_DeptAbbr] AS

    DECLARE @Department varchar
    DECLARE @getRecords CURSOR

    Set @getRecords = CURSOR FOR  Select Department EKP_StaffDetails

    OPEN @getRecords
    FETCH NEXT FROM @getRecords INTO @Department
    WHILE @@FETCH_STATUS= 0
    BEGIN
    Left(@getRecords, 1) ' get first letter
    CharIndex(' ', @getRecords)
    Get 2nd letter after space??

    FETCH NEXT FROM @getRecords INTO @Department
    END

    After I get the first character using the line

    Left(aGetRecords,1)

    I don't really know where to go with it. I can find the next space but then I don't know how to get the 1st letter after that space and so on.
    Friday, September 12, 2008 11:20 AM

Answers

  •  

    I mentioned that code only works on SQL Server 2005 or 2008, since xml datatype doesn't exist in 2000.

     

    For SQL Server 2000 use this:

     

    Code Snippet

    declare @string varchar(8000)

    set @string = 'some words with no sense'

    declare @TableOfWords table(word varchar(1000))

     

    declare @LenString int

    while len( @string ) > 0

    begin

    select @LenString =

    (case charindex( ' ', @string )

    when 0 then len( @string )

    else ( charindex( ' ', @string ) -1 )

    end

    )

    insert into

    @TableOfWords

    select

    substring( @string, 1, @LenString )

    select @string =

    (case ( len( @string ) - @LenString )

    when 0 then ''

    else right( @string, len( @string ) - @LenString - 1 )

    end

    )

    end

     

     

    declare @result varchar(8000)

    set @result = ''

     

     

    select

    @result = @result + left(word,1)

    from

    @TableOfWords

     

    select @result

     

     

    Friday, September 12, 2008 2:34 PM

All replies

  •  

    Hi Guddu,

     

    If you are running SQL Server 2005 or 2008 you can use something like this:

     

    Code Snippet

    declare @string varchar(8000)

    set @string = 'some words with no sense'

    declare @textXML xml

    select @textXML = cast('<d>' + replace(@string, ' ', '</d><d>') + '</d>' as xml)

    declare @result varchar(8000)

    set @result = ''

    select

    @result = @result + left(T.split.value('.', 'nvarchar(max)'),1)

    from

    @textXML.nodes('/d') T(split)

    select @result

     

     

    Friday, September 12, 2008 11:31 AM
  • hi

    on sql server 2005 its working perfectly.but on sql server 2000 i m getting error at this line

    @textXML.nodes('/d') T(split)

     

     

    i want this code should work on sql server 2000.

     

    Thanks a lot

     

    Friday, September 12, 2008 1:46 PM
  •  

    I mentioned that code only works on SQL Server 2005 or 2008, since xml datatype doesn't exist in 2000.

     

    For SQL Server 2000 use this:

     

    Code Snippet

    declare @string varchar(8000)

    set @string = 'some words with no sense'

    declare @TableOfWords table(word varchar(1000))

     

    declare @LenString int

    while len( @string ) > 0

    begin

    select @LenString =

    (case charindex( ' ', @string )

    when 0 then len( @string )

    else ( charindex( ' ', @string ) -1 )

    end

    )

    insert into

    @TableOfWords

    select

    substring( @string, 1, @LenString )

    select @string =

    (case ( len( @string ) - @LenString )

    when 0 then ''

    else right( @string, len( @string ) - @LenString - 1 )

    end

    )

    end

     

     

    declare @result varchar(8000)

    set @result = ''

     

     

    select

    @result = @result + left(word,1)

    from

    @TableOfWords

     

    select @result

     

     

    Friday, September 12, 2008 2:34 PM
  •  

    Great Post! I was also looking into making this script to work inside the transaction which I fiigured out how to do after looking on this site: http://forum.visualstudioteamsystem.com/details.aspx?postid=90 

     

    I am planning to create a funcation and keep it as my library of functions in my SQL project.

    Friday, September 12, 2008 7:04 PM
  • Good stuff Cristian,

    I'm combining your solution with "For XML" to use in a sql statement

    SELECT

     

    'QARCH_CNFG_CTRL' TABLE_NAME
    INTO #TMP
    UNION SELECT 'QARCH_QUEU_PROCESS'
    UNION SELECT 'QARCH_QUEU_PROCESS_PARAM'
    UNION SELECT 'QARCH_QUEU_PROCESS_STEP'
    UNION SELECT 'QARCH_QUEU_PROCESS_PROCESS_STEP_PARAM'

    SELECT TABLE_NAME ,( select (select left(ParamValues.td.value('.', 'nvarchar(max)'),1)
                                              FROM textXML.nodes('/td') as ParamValues(td)
                                              FOR XML PATH('')) INIT_CAP

     

                                     from (select textXML = cast('<td>' + replace(TABLE_NAME, '_', '</td><td>') + '</td>' as xml)) t ) RESULT
    FROM #TMP

     

    Thursday, July 29, 2010 8:02 PM
  • Here is a simple SQL User Defined Function that will return the upper case abbreviation for most versions of SQL:

    CREATE FUNCTION dbo.Abbreviate ( @InputString varchar(1000) )
    RETURNS VARCHAR(100)
    AS
    BEGIN
        DECLARE @Index INT
        DECLARE @OutputString VARCHAR(100)

        SET
    @InputString = LTRIM(@InputString)
        SET @OutputString = UPPER(LEFT(@InputString, 1))
        SET @Index = CHARINDEX(' ', @InputString) + 1

        WHILE @Index >
        BEGIN
            SET @OutputString = @OutputString + UPPER(SUBSTRING(@InputString, @Index, 1)) 
            SET @Index = CHARINDEX(' ', @InputString, @Index) + 1
        END
        RETURN @OutputString
    END
    GO

    -- To Run it:

    SELECT

     

    dbo.Abbreviate ('eastern standard time')

    Tuesday, January 04, 2011 8:39 PM
  • It picks up isolated hyphens, skips word after imbedded hyphen.

    SELECT ProductName=Name, ProductCode=dbo.Abbreviate (Name)
    FROM AdventureWorks2008.Production.Product
    ORDER BY ProductCode
    /*
    ProductName	ProductCode
    All-Purpose Bike Stand	ABS
    AWC Logo Cap	ALC
    Adjustable Race	AR
    Blade	B
    Bearing Ball	BB
    BB Ball Bearing	BBB
    Bike Wash - Dissolver	BW-D
    Chain	C
    Chainring	C
    Chainring Bolts	CB ....*/
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, January 04, 2011 8:58 PM
    Moderator
  • Ok, if you want to handle additional delimitors then you could use something with PATINDEX instead. Here is a slightly modified version that should work. Just set the @Delims to the list of delimitors (between the [ ])

    CREATE

     

    FUNCTION dbo.Abbreviate ( @InputString varchar(1000) )
    RETURNS VARCHAR(100)
    AS
    BEGIN
        DECLARE @Index INT
        DECLARE @Char CHAR(1)
        DECLARE @PrevChar CHAR(1)
        DECLARE @OutputString VARCHAR(100)
        DECLARE @Delims VARCHAR(100)

        SET @Delims = '%[ ).(;-]%'
        SET @InputString = LTRIM(@InputString)
        SET @OutputString = UPPER(LEFT(@InputString, 1))
        SET @Index = PATINDEX(@Delims, @InputString) + 1

        WHILE @Index > 1 AND @Index < LEN(@InputString)
        BEGIN
            SET @InputString = SUBSTRING(@InputString, @Index, 100)
            SET @OutputString = @OutputString + 
                CASE WHEN PATINDEX(@Delims, @InputString) <> 1 THEN UPPER(SUBSTRING(@InputString, 1, 1)) 
                ELSE '' END
            SET @Index = PATINDEX(@Delims, @InputString) + 1
        END
        RETURN @OutputString
    END
    GO

    Wednesday, January 05, 2011 7:45 PM
  • Hi,

    thanks prkramer A LOT for posting the script, it was very useful for me

    I changed it a bit, to fit my own needs of abbreviation

    (it was very, easy since your initial script is very good written and consistent)

    I added one parameter indicating amount of chars taken (@noc),

    another for the separator between "tokens" (@space)

    and one more for trimming or not (@trim =0/1)

     

    FUNCTION dbo.fn_Abbreviate( @InputString varchar(100), @noc tinyint, @space varchar(10), @trim tinyint )
    RETURNS VARCHAR(100)
    AS
    BEGIN
    	-- ejemplo
    	-- select dbo.fn_Abbreviate('Information Technology or other Stuff',3,'..',0)
    	-- returns : INF..TEC..OR ..OTH..STU
    
      DECLARE @Index INT
      DECLARE @Char CHAR(1)
      DECLARE @PrevChar CHAR(1)
      DECLARE @OutputString VARCHAR(100)
      DECLARE @Delims VARCHAR(100)
    	declare @txt varchar(100)
    
      SET @Delims = '%[ ).(;-]%'
      SET @InputString = LTRIM(@InputString)
      SET @txt= UPPER( left( @InputString, @noc))
    	if @trim=1 set @txt= ltrim(rtrim(@txt))
    	SET @OutputString = @txt+ @space
      SET @Index = PATINDEX(@Delims, @InputString) + 1
    
      WHILE @Index > 1 AND @Index < LEN(@InputString)
      BEGIN
        SET @InputString = SUBSTRING(@InputString, @Index, 100)
        SET @txt= 
          CASE WHEN PATINDEX(@Delims, @InputString) <> 1 THEN UPPER(SUBSTRING(@InputString, 1, @noc)) 
          ELSE '' END
    		if @trim=1 set @txt= ltrim(rtrim(@txt))
    		SET @OutputString = @OutputString + @txt+ @space
        SET @Index = PATINDEX(@Delims, @InputString) + 1
      END
      RETURN @OutputString
    END
    GO
    

     

    Wednesday, June 15, 2011 3:31 PM
  • Sure!

    1)

    select dbo.fn_Abbreviate('Information Technology or other Stuff',3,'..',0)

    returns: INF..TEC..OR ..OTH..STU

    there is a space after "OR", this is because we don't want trimming (last parameter=0)

    2)

    select dbo.fn_Abbreviate('Information Technology or other Stuff',3,'..',1)

    returns: INF..TEC..OR..OTH..STU

    there is no space after "OR", this is because we want trimming (last parameter=1)

    3)

    select dbo.fn_Abbreviate('Information Technology or other Stuff',5,'#',1)

    returns: INFOR#TECHN#OR OT#OTHER#STUFF --> *** BE CAREFUL, THERE IS A BUG HERE ***

    see the "OR OT"... I'm going to check it and then I'll repost

    4)

    select dbo.fn_Abbreviate('Information Technology or other Stuff',1,'',1)

    returns: ITOOS

     

    Friday, June 17, 2011 2:39 PM