Searching for a specfic character 2

[awaria]

I need help with a query where I need to query a string field, determine if there is a "/" in the string.
If so, start at the next position and return the next 2 characters. If there is no "/" in the string, set value to 1.

Appreciate help with this one.

Thanks,

Andrew

 

[simian336]

declare @myvar as varchar(20)

set @myvar ='1234/5678'

select case
when cHARINDEX ('/', @myvar) > 0
then SUBSTRING ( @myvar ,cHARINDEX ('/', @myvar)+1, 2)
else '1'
end as bla


Simi

 

[awaria]

Simi,

Thanks for the post.

I'm doing something wrong as all my results are the false or Else with the value of 1, whereas, the majority of the string values in the field [pop30310.uofm] have a "/" somewhere in the string.

Here's is my syntax, can you evaluate.

Thanks again,

Andrew

/*
declare @myvar as varchar(20)
set @myvar ='pop30310.uofm' <- table.field

select case when cHARINDEX ('/', @myvar) > 0
then SUBSTRING ( @myvar ,cHARINDEX ('/', @myvar)+1, 2)
else '1'
end as [UofM Conversion Factor]
from pop30310
*/

 

[djj55]

In SQL Server you do not write dynamic code in this way. If you define @myvar as a VARCHAR it will put the value in the code as in

select case     when cHARINDEX ('/', 'pop30310.uofm') > 0          
then SUBSTRING ( @myvar ,cHARINDEX ('/', 'pop30310.uofm')+1, 2)     
else '1'
end as [UofM Conversion Factor]
from pop30310

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Sorry hit the wrong button.

What you really want is

select case     when cHARINDEX ('/', [b]uofm[/b]) > 0          
then SUBSTRING ( @myvar ,cHARINDEX ('/', [b]uofm[/b])+1, 2)     
else '1'
end as [UofM Conversion Factor]
from pop30310

So if you need to have the column name dynamic it will take using an execute statement. Let us know if you need help.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[simian336]

That was just an example on a single string...

You need...

select case
when cHARINDEX ('/', uofm) > 0
then SUBSTRING ( uofm ,cHARINDEX ('/', uofm)+1, 2)
else '1'
end as bla
from pop30310

Simi

 

[simian336]

just an FYI, that '1' that gets returned is a Character '1' not an integer 1.

Simi

 

[awaria]

Works perfectly.

Thanks Simi!
Thanks everyone for your lending your expertise.

Very Gratefully,

Andrew