Right-Left never done before 2

[Ladyazh]

I had never used Right or Left function creating a views.
I need to have field [NAMEOFTHETABLE]=12345
to trim to have everything before =.
In Crystal I would use Instr function but in a query I am not sure how to do this.

Please, help.
Thank you.

 

[AlexCuse]

If there will always have an equal sign, this will work for you:

select left('[NAMEOFTHETABLE]=12345', charindex('=','[NAMEOFTHETABLE]=12345')-1)

Hope his helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

ps charindex in SQL Server is the equivalent of instr in vb, it returns the starting position of your search string

Ignorance of certain subjects is a great part of wisdom

 

[Ladyazh]

AlexCuse, nice to see you here!
Thanks

 

[Ladyazh]

Something doesn't work, I think i misled you.

There is a column APPCD
that has data
[NAMEOFTHETABLE]=1234
[NAMEOFTHETABLE]=12346
[NAMEOFTHETABLE]=345
[NAMEOFTHETABLE]=12340

So I want to have numbers after = only!

Thanks

 

[gmmastros]

Maybe....

[COLOR=blue]Select[/color] ParseName([COLOR=#FF00FF]Replace[/color](APPCD, [COLOR=red]'='[/color], [COLOR=red]'.'[/color]), 1)
[COLOR=blue]From[/color]   [!]TableName[/!]

-George

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

 

[monksnake]

Make it this:

select right('[NAMEOFTHETABLE]=12345', len('[NAMEOFTHETABLE]=12345') - charindex('=','[NAMEOFTHETABLE]=12345'))

*tested

<.

 

[Ladyazh]

Hi,
you are using [NAMEOFTHETABLE]=1234, but what if it is [NAMEOFTHETABLE]=123499999???

I am going to try to modify. Thanks

I want it to look like this
1234
12346
345
12340

 

[AlexCuse]

Or, the same thing but taking what's to the right of the equal sign rather than left:

[COLOR=blue]select[/color] [COLOR=#FF00FF]left[/color](APPCD, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'='[/color],[COLOR=#FF00FF]reverse[/color](APPCD))-1)

The only change was to replace left with right, and reverse the string prior to charindex, so that we are counting backwards from the end.

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Or... maybe that cool ParseName function that I showed a couple posts up?

I tested it like this...

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](APPCD [COLOR=blue]varchar[/color](100))

[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'[NAMEOFTHETABLE]=1234'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'[NAMEOFTHETABLE]=12346'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'[NAMEOFTHETABLE]=345'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'[NAMEOFTHETABLE]=12340'[/color])

[COLOR=blue]Select[/color] ParseName([COLOR=#FF00FF]Replace[/color](APPCD, [COLOR=red]'='[/color], [COLOR=red]'.'[/color]), 1)
[COLOR=blue]From[/color]   @Temp

-George

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

 

[Ladyazh]

Alex, it works with Right, thanks!!!!

 

[monksnake]

Or... maybe that cool ParseName function that I showed a couple posts up?

heh George.

Mine worked too, I actually tested it!! However I'd take either method besides mine as they are shorter.



<.

 

[Ladyazh]

gmmastros,
I thought your suggestion will replace '=' with '.' and this is what I did not need. I shell try if it is not going to give me the dot.

Select ParseName(Replace(APPCD, '=', '.'), 1)
From TableName


monksnake,
yours i thought will work only for the [NAMEOFTHETABLE]=12345 because it was hard coded into the function.
Am I wrong?

I am not writing procedure, I am writing select statement.

Please, feel free to overturn my opinion.


I ran into the thing now that the field I've got is String type (after stripping that [NAMEOFTHETABLE]= portion.
I need to link it to the table where same field is Numeric and SQl will not let me. Ofcourse. What should I do? Some convert into Numeric way?

Thanks you guys are funny!