?? SubString like option for an INT value ??

[Haybails90]

Hey there SQL Pros,

I'm looking for some assistance on my SQL coding. I'm looking for a SQL solution to the following scenario: I'd like to use something similar to SubString but on an INT value. For example, let's say the INT value is 123457890, I need a way to check, say, the fifth numeral in that value. I'll be throwing this into the WHERE portion of my existing SQL call. All assistance is greatly appreciated.




Haybails90

 

[donutman]

You can do it with math.

[Blue]DECLARE[/Blue] @I [Blue]AS[/Blue] [Blue]int[/Blue] 
[Blue]SET[/Blue] @I[Gray]=[/Gray][Gray]([/Gray]1234567890/10000[Gray])[/Gray][Gray]%[/Gray]10
[Blue]SELECT[/Blue] @I

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Haybails90]

Um ... huh?

 

[Haybails90]

This will be SQL code in an ASP page and I test it in Query Analyzer.

 

[Haybails90]

Sorry, I just figured it out by myself. I completely forgot about one of the most basic functions - LOL. My only defense is "Monday Madness". Here's what I did:

SubString(Str(employees.job_nbr), 4, 1) = '1'

Sorry, again, for the interuption; now, GET BACK TO WORK!!! <G>





Haybails90

 

[vongrunt]

What if job_nbr has variable number of digits?

 

[Haybails90]

LOL!! vongrunt - you just caught me!!

I just stumbled upon this exact issue with my data. Is there a way to count 'backwards' from the end of the string?




Haybails90

 

[donutman]

Ok, vongrunt. What's the math approach for the 5th from the left?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Haybails90]

donutman,

My superior and I were just discussing this (by the way, I REALLY HATE, with a passion might I add, the developer whom I replaced - LOL). So, now I'll be needing to come from the other end - I.E. the right end of the string back towards the front/left.

 

[donutman]

So, I got it right the first time...Wahhoo!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Haybails90]

Would it be rediculous to do it like this:

LEFT(RIGHT(Str(job_nbr),6),1)

This would give me the sixth numeral from the right end of the job number.




Haybails90

 

[donutman]

Fine if you want the 6th from right and the number always has at least 6 digits. You could add a few 0's to the Str in order to assure that. But I'd like a purely math approach and can't think of one.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Got confused here... let's say there are two numbers: 1234567890 and 34567890. Haybails90: do you need

a) 5 and 5... or
b) 5 and 7?

If answer is a), then even your original query should work because STR() returns left-padded char(10) by default. For b) try

declare @i int; set @i = 1234567487
declare @ldigit tinyint; set @ldigit = 5

--- ugly math approach
select @i/power(10, 1 + cast(log10(@i) as int) - @ldigit) % 10
-- good ol' banzai method
select substring(convert(varchar, @i), @ldigit, 1)

 

[donutman]

I was pretty sure you could do it. GJ! You'd probably like this link from rudy too.

http://rudy.ca/doomsday.html

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]