Query no longer works 2

[DrSeussFreak]

SELECT Right(em_id, CharIndex(' ', Reverse(em_id) + ' ') - 1) em_id
FROM afm.em

This query was working until recently (someone changed some field types and they are now back to what they were before). em_id is a varchar(100) field.

the data looks like

Freak, Seuss D. 1234567489 OR
Freak, Seuss 123456789

I need to pull just the numbers and not the text.


Ideas?

 

[DrSeussFreak]

they could also be

Freak, Seuss D 12345679

D.
D
or no Middle Initial

 

[gmmastros]

I think that em_id is probably a Char(100) field (not varchar).

Ex:

Declare @em_id Char(100)

Set @em_id = 'Freak, Seuss 123456789'

SELECT Right(@em_id, CharIndex(' ', Reverse(@em_id) + ' ') - 1) em_id

This doesn't work. But, if you change the Char(100) to VarChar(100) it does.

Anyway... This should solve your problem.

SELECT Right([!]RTrim([/!]em_id[!])[/!], CharIndex(' ', Reverse([!]RTrim([/!]em_id[!])[/!]) + ' ') - 1) em_id
FROM afm.em

-George

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

 

[JBaileys]

A very interesting approach...

Not sure why you would want to use the reverse function to reverse the string.

If I understand your request, you want to take "Freak, Seuss D. 1234567489" and return 1234567489

Have you tried CAST ( em_id AS int ) ?

 

[gmmastros]

JBaileys,

The idea here is that the number is at the end of the string, so by reversing it, you can get the last 'word' (searching on the space). If you simply cast the number as an integer, you will get a syntax error (when you run it).

Try it:

Declare @em_id Char(100)

Set @em_id = 'Freak, Seuss 123456789'

select cast(@em_id as int)

[red]Syntax error converting the varchar value 'Freak, Seuss 123456789 [/red]

-George

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

 

[DrSeussFreak]

@gmmastros - Worked great thanks!

@JBaileys - I have not tried the CAST (em_id AS int), I had done this once before and it worked. For some reason it stopped. Can you please give example code for this? It will be good to have.

Thanks guys.

DSF

 

[DrSeussFreak]

that makes sense about the cast int.

And for why I use the reverse, exactly like he said.

 

[onpnt]

You can't CAST a string to an int. Not sure how that would have ever worked.

Or am I missing something cause now I want to know. That just seems against the laws of data type conversion all around

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[DrSeussFreak]

it doesn't work, that is what we are saying.

 

[DrSeussFreak]

A is not an int unless you convert it to it's ASCII value.

 

[onpnt]

Sorry, that was directed towards JBaileys ;-)

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[DrSeussFreak]

lol, no problem