none
String Truncate

    Pergunta

  • I have the below select query which returns

    select SUBSTRING(REPLACE([Path],'STRING/',''),0,CHARINDEX('/',REPLACE([Path],'STRING/',''))),M.[Path]
    FROM [uf_GetInput] ('STRING','INPUT') M

    Input [Path]

    STRING/SC0000
    STRING/SCDTTM
    STRING/SETTT
    STRING/SETTT/SCTT10
    STRING/SCD/SCM869
    STRING/SC0/SC1890

    When I use the query above it returns the values perfectly as long as the path value is STRING/SETTT/????? and returns "EMPTY" string if the PATH value is STRING/????

    Output [Path]

    STRING/SC0000  --> Empty (Expecting SC0000)
    STRING/SCDTTM -->  Empty (Expecting SCDTTM)
    STRING/SETTT -->   Empty (Expecting SETTT)
    STRING/SETTT/SCTT10 --> As Expected
    STRING/SCD/SCM869 --> As Expected
    STRING/SC0/SC1890 --> As Expected

    I need help in changing this query .......SUBSTRING(REPLACE([Path],'STRING/',''),0,CHARINDEX('/',REPLACE([Path],'STRING/',''))),M.[Path]...... so that it works for both the conditions.

    Thanks




    Please click "Mark as Answer" if the post solves your problem - Thanks


    terça-feira, 20 de março de 2012 18:29

Respostas

  • Or something like this:

    CASE CHARINDEX('/',REPLACE([Path],'STRING/',''))
    	WHEN 0 THEN REPLACE([Path],'STRING/','')
    	ELSE SUBSTRING(REPLACE([Path],'STRING/',''),0,CHARINDEX('/',REPLACE([Path],'STRING/','')))
    END,M.[Path]

    David.

    • Marcado como Resposta Venugopal Saride terça-feira, 20 de março de 2012 20:12
    terça-feira, 20 de março de 2012 18:38
  • Declare @myTable Table (ColPath Varchar(Max))
    Insert Into @myTable
    Select 'STRING/SC0000' Union All 
    Select 'STRING/SCDTTM' Union All 
    Select 'STRING/SETTT' Union All 
    Select 'STRING/SETTT/SCTT10' Union All 
    Select 'STRING/SCD/SCM869' Union All 
    Select 'STRING/SC0/SC1890'
    
    Select *, SubString(ColPath, CharIndex('/', ColPath) + 1, (Case When CharIndex('/', SubString(Colpath, CharIndex('/', ColPath) + 1, Len(ColPath))) = 0 Then Len(ColPath) Else CharIndex('/', SubString(Colpath, CharIndex('/', ColPath) + 1, Len(ColPath))) - 1 End)) As NewOutput
    From @myTable
    
    --output
    ColPath	NewOutput
    STRING/SC0000	SC0000
    STRING/SCDTTM	SCDTTM
    STRING/SETTT	SETTT
    STRING/SETTT/SCTT10	SETTT
    STRING/SCD/SCM869	SCD
    STRING/SC0/SC1890	SC0


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marcado como Resposta Venugopal Saride terça-feira, 20 de março de 2012 20:12
    terça-feira, 20 de março de 2012 18:41
  • Try:

    select SUBSTRING(REPLACE([Path] + '/','STRING/',''),0,CHARINDEX('/',REPLACE([Path] + '/','STRING/',''))),M.[Path]
    FROM [uf_GetInput] ('STRING','INPUT') M


    AMB

    Some guidelines for posting questions...

    • Marcado como Resposta Venugopal Saride terça-feira, 20 de março de 2012 20:12
    terça-feira, 20 de março de 2012 18:43
    Moderador

Todas as Respostas

  • Hi, compare the value for CHARINDEX with 0 and in that case use some other value. Something like this:

    SUBSTRING(REPLACE([Path],'STRING/',''),0,CASE CHARINDEX('/',REPLACE([Path],'STRING/',''))) WHEN 0 THEN 100 ELSE CHARINDEX('/',REPLACE([Path],'STRING/',''))) END,M.[Path]

    David.

    terça-feira, 20 de março de 2012 18:34
  • Or something like this:

    CASE CHARINDEX('/',REPLACE([Path],'STRING/',''))
    	WHEN 0 THEN REPLACE([Path],'STRING/','')
    	ELSE SUBSTRING(REPLACE([Path],'STRING/',''),0,CHARINDEX('/',REPLACE([Path],'STRING/','')))
    END,M.[Path]

    David.

    • Marcado como Resposta Venugopal Saride terça-feira, 20 de março de 2012 20:12
    terça-feira, 20 de março de 2012 18:38
  • Declare @myTable Table (ColPath Varchar(Max))
    Insert Into @myTable
    Select 'STRING/SC0000' Union All 
    Select 'STRING/SCDTTM' Union All 
    Select 'STRING/SETTT' Union All 
    Select 'STRING/SETTT/SCTT10' Union All 
    Select 'STRING/SCD/SCM869' Union All 
    Select 'STRING/SC0/SC1890'
    
    Select *, SubString(ColPath, CharIndex('/', ColPath) + 1, (Case When CharIndex('/', SubString(Colpath, CharIndex('/', ColPath) + 1, Len(ColPath))) = 0 Then Len(ColPath) Else CharIndex('/', SubString(Colpath, CharIndex('/', ColPath) + 1, Len(ColPath))) - 1 End)) As NewOutput
    From @myTable
    
    --output
    ColPath	NewOutput
    STRING/SC0000	SC0000
    STRING/SCDTTM	SCDTTM
    STRING/SETTT	SETTT
    STRING/SETTT/SCTT10	SETTT
    STRING/SCD/SCM869	SCD
    STRING/SC0/SC1890	SC0


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marcado como Resposta Venugopal Saride terça-feira, 20 de março de 2012 20:12
    terça-feira, 20 de março de 2012 18:41
  • Try:

    select SUBSTRING(REPLACE([Path] + '/','STRING/',''),0,CHARINDEX('/',REPLACE([Path] + '/','STRING/',''))),M.[Path]
    FROM [uf_GetInput] ('STRING','INPUT') M


    AMB

    Some guidelines for posting questions...

    • Marcado como Resposta Venugopal Saride terça-feira, 20 de março de 2012 20:12
    terça-feira, 20 de março de 2012 18:43
    Moderador