Need help with instring update statement

[mikej336]

I have three fields, Filename, Actnum, Branch

In Filename field I have the names of a pdf files like

0000000001.pdf
0000000001_001.pdf
0000000002.pdf

I need to seperate that name into the other 2 fields

so that Actnum=0000000001 and Branch=001 and get rid of the .pdf

If 001 does not exist then Branch is just blank.

Any Thoughts

Thanks

Uncle Mike

 

[gmmastros]

Charindex and left are your friends here.

Take a look at this example.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](FileName [COLOR=blue]VarChar[/color](100), ActNum [COLOR=blue]VarChar[/color](100), Branch [COLOR=blue]VarChar[/color](100))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(FileName) [COLOR=blue]Values[/color]([COLOR=red]'0000000001.pdf'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(FileName) [COLOR=blue]Values[/color]([COLOR=red]'0000000001_001.pdf'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(FileName) [COLOR=blue]Values[/color]([COLOR=red]'0000000002.pdf'[/color])

[COLOR=blue]Select[/color] FileName, 
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'_'[/color], FileName) > 0
            [COLOR=blue]Then[/color] [COLOR=#FF00FF]Left[/color](FileName, [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'_'[/color], FileName) - 1)
            [COLOR=blue]Else[/color] [COLOR=#FF00FF]Left[/color](FileName, [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'.'[/color], FileName) - 1)
            [COLOR=blue]End[/color] [COLOR=blue]As[/color] ActNum,
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'_'[/color], FileName) > 0
            [COLOR=blue]Then[/color] [COLOR=#FF00FF]SubString[/color](FileName, [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'_'[/color], FileName) + 1, [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'.'[/color], FileName) -[COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'_'[/color], FileName) - 1)
            [COLOR=blue]Else[/color] [COLOR=red]''[/color]
            [COLOR=blue]End[/color] [COLOR=blue]As[/color] Branch
[COLOR=blue]From[/color]   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Look at SUBSTRING, PATINDEX, and CHARINDEX in the BOL.

Use PATINDEX to find the position of the period. Then grab the SUBSTRING up to the period and put that into a variable. Next use the PATINDEX to find the underscore, if there is one, put what follows into another variable.

-SQLBill

Posting advice: FAQ481-4875