none
Part of string Help again

    Question

  • select

    replace(right('BAAU  1864175*2*201312170930*MBNP',CHARINDEX('_','BAAU  1864175*2*201312170930*MBNP')+27),'*','_')

    It returns the result below

    1864175_2_201312170930_MBNP

    This is the result i want however I want it by subtracting the first words before the first space i.e in this case 'BAAU', the code i wrote is not actually doing this as it just adding +27 characters from the right but in some cases i have more than 27 characters after the first space, can some one please help me figure this out.

    Many thanks

    Wednesday, March 05, 2014 12:51 PM

Answers

All replies

  • DECLARE @text NVARCHAR(MAX) = 'BAAU  1864175*2*201312170930*MBNP'
    
    SELECT SUBSTRING(@text, CHARINDEX(' ', @text), LEN(@text) - CHARINDEX(' ', @text) + 1 )


    sqldevelop.wordpress.com

    Wednesday, March 05, 2014 1:05 PM
  • poppy,

    check if this helps:

    declare @ch varchar(max)
    set @ch='BAAU  1864175*2*201312170930*MBNP'
    
    select replace(stuff(@ch,1,charindex(' ',@ch)+1,''),'*','_')


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, March 05, 2014 1:14 PM
  • It worked thanks,

    Since you seem to know your stuff , Can I ask for some help in working out  the following string 1864175*2* and replacing the * with _ and making sure the string picks up any addtional numbers before the second star sometimes I have more than one number in between the stars

    from the string below.

    'BAAU  1864175*2*201312170930*MBNP'

    thanks for all your help.

    Wednesday, March 05, 2014 3:28 PM
  • Hi Poppy2014, please try below..

    DECLARE @Input VARCHAR(100)= 'BAAU  1864175*2*201312170930*MBNP'
    
    SELECT LTRIM(SUBSTRING(REPLACE(@Input, '*', '_'), CHARINDEX(' ', @Input, 1), LEN(@Input))) AS [Output]

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Wednesday, March 05, 2014 3:36 PM
  • just this?

    if string has no . or space characters  already this is enough

    PARSENAME(replace(replace('BAAU  1864175*2*201312170930*MBNP','*','_'),' ','.'),1)

    Another method


    SELECT Val
    FROM dbo.ParseValues('BAAU  1864175*2*201312170930*MBNP',' ')f
    WHERE ID = 2


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 05, 2014 4:05 PM