SQL Server Performance

How to return string values.

Discussion in 'SQL Server 2005 General Developer Questions' started by starwarsbigbang, Jul 28, 2008.

  1. starwarsbigbang New Member

    Is it possible to return only the upper case of a given string. For a string say, 'My name Is John Cramps' I should get the output as 'MIJC' I am using SQL Server 2005.


    starwarsbigbang, Jul 28, 2008
    #2

  2. FrankKalis Moderator

    Yes, that is possible when you parse the string and only return those characters within a certain ASCII range. But why? And why in SQL Server and not in a client language?
    FrankKalis, Jul 29, 2008
    #3

  3. starwarsbigbang New Member

    Thank you Frank. Can you please help me with the code.
    starwarsbigbang, Jul 29, 2008
    #4

  4. FrankKalis Moderator

    [quote user="starwarsbigbang"]
    Thank you Frank. Can you please help me with the code.
    [/quote]
    Here's one way:
    DECLARE @s varchar(100)
    SELECT @s = 'My name Is John Cramps'

    SELECT
    x.OnlyInitials
    FROM
    (SELECT
    SUBSTRING(@s, N.Number, 1)
    FROM
    dbo.Number N
    WHERE
    N.Number BETWEEN 0 AND LEN(@s) AND
    ASCII(SUBSTRING(@s, N.Number, 1)) BETWEEN 65 AND 90
    FOR XML PATH('')) AS x(OnlyInitials)
    OnlyInitials
    ------------
    MIJC

    (1 row(s) affected)
    FrankKalis, Jul 29, 2008
    #5

  5. Adriaan New Member

    Or a more classic string-based solution:
    DECLARE @S VARCHAR(100)
    SET @S = 'My name Is John Cramps'
    DECLARE @T VARCHAR(100), @X VARCHAR(1), @I INT, @J INT
    SET @I = LEN(@S)
    SET @J = 1
    SET @T = ''
    WHILE @J <= @I
    BEGIN
    SET @X = SUBSTRING(@S, @J, 1)
    IF @X <> ' ' AND ASCII(@X) = ASCII(UPPER(@X))
    BEGIN
    SELECT @T = @T + @X
    END
    SET @J = @J + 1
    END
    SELECT @T
    Adriaan, Jul 29, 2008
    #6

  6. Madhivanan Moderator

    This is another appraoch. This will work well even if the string has upper case in the middle of a stringDECLARE
    @S VARCHAR(100)SET
    @S = 'My name Is John CraMps' DECLARE
    @T VARCHAR(100), @X VARCHAR(1)SET
    @T = ''WHILE CHARINDEX(' ',@S)>0
    BEGIN
    SET @X = SUBSTRING(@S, 1, CHARINDEX(' ',@S)+1)
    SELECT @T = @T + CASE WHEN ASCII(@X) = ASCII(UPPER(@X)) THEN @X ELSE '' END
    SET @S = SUBSTRING(@S, CHARINDEX(' ',@S)+1,len(@S))
    END
    SET
    @X = SUBSTRING(@S, 1, 1)SELECT @T = @T + CASE WHEN ASCII(@X) = ASCII(UPPER(@X)) THEN @X ELSE '' END
    SELECT
    @T
    Madhivanan, Aug 7, 2008
    #7

Share This Page