Post reply

Insert spaces before upper case characters

  • March 19, 2009 at 8:59 am

    #127175

    Hi,

    Is it possible to write a script which inserts a space before all upper case characters in a string expression (apart from the first character obviously)?

    thanks

    Lloyd

  • March 19, 2009 at 9:11 am

    #962789

    It should be.

    I think you can use STUFF() function for that.

    Use that at index of each character that is an uppercase.

    PS:

    The only reason I feel lazy to write the script out is because I suspect this to be homework.

    How To Post[/url]

  • March 19, 2009 at 9:15 am

    #962800

    Cheers but this isnt homework, im looking for this code so the guy that builds our ssis packages can insert spaces in the field names of our tables in the data warehouse. any help with the script would be much appreciated.

  • March 19, 2009 at 10:02 am

    #962847

    I am at work so cannot spend too much time on this....

    Something along the following lines should work:

    declare @input varchar(30)

    declare @output varchar(900)

    set @input = 'ThisIsNotGood.ButWillCheckItOut'

    select @output = case

    When ascii(substring(@input, N, 1)) between 65 and 90 Then @output + ' ' + substring(@input, N, 1)

    else @output + substring(@input, N, 1)

    end

    From Tally

    where N<len(@input)

    print 'result:'

    print @output

    Point to note: This is not working ... I need to debug it.

    Point to note: I use tally table for this. If you don't know what a tally table is, its a basic table of a sequence of integers.

    Search this site for a script on how to create one.

    How To Post[/url]

  • March 19, 2009 at 10:05 am

    #962852

    cheers i'll take a look

  • March 19, 2009 at 10:25 am

    #962881

    just incase you need it.

    Here is a solution for if you data is stored in table and you want to do more than one row at a time.

    DECLARE @table TABLE

    (Input VARCHAR(MAX))

    INSERT INTO @Table

    SELECT 'IReallyHopeThisWorks.' UNION ALL

    SELECT 'IfNotPleaseLetMeKnow'

    SELECT

    STUFF((SELECT

    CASE

    WHEN PATINDEX('%[A-Z]%',SUBSTRING(Input,N,1) COLLATE Latin1_General_BIN) != 0

    THEN ' ' + SUBSTRING(Input,N,1)

    ELSE SUBSTRING(Input,N,1)

    END

    FROM Tally WHERE N <=LEN(Input) FOR XML PATH('')),1,1,'')

    FROM @table

    This also uses a tally table

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply