January 31, 2013 at 1:06 pm
Contrary to every article listed in a Google Search, I actually need my alpha characters. Here's what I'm trying to do.
I need help with some logic to convert alpha characters to numeric values. Each site has a site ID. This column is a VARCHAR(15) so, of course, you give an end user an option, they'll use it. Now I have 100+ site id's with a dozen or so ending in AA. For example, 1000, 2000, 3000, 3000AA, 4000, ... Obviously, if I follow the CAST or CONVERT logic, the 3000AA will through an error:
Conversion failed when converting the varchar value '3000AA' to data type int.
What I'm trying to do is
AVG(CAST(TableA.SITE_ID AS INT)) OVER ( PARTITION BY TableB.NUMBER ) AVG_SITE_ID
I later want to find:
CTE.SITE_ID <> CTE.AVG_SITE_ID
Absolutes:
First: Every single Alpha character will BE AA
Second: Every single Alpha character will be at END (RIGHT 2 characters) of the numeric values
Third: Every single numeric value will be four digits (before AA).
Goal: Convert AA to 1 so values would be 1000, 2000, 3000, 30001, 4000 ...
There has to he a way to (pseudo) IF Site ID right two = AA THEN LEFT 4 + convert AA == 1 ELSE next
What I can't do is take 3000AA and make it 3001, it needs to be 30001. Maybe a way to declare it in the CTE so I could:
AVG(CAST(TableA.@site AS INT)) OVER ( PARTITION BY TableB.NUMBER ) AVG_SITE_ID
If you have references or logic ideas, please feel free to share. I understand that there's not much code here so I'm really looking for spitball ideas versus actual working code.
January 31, 2013 at 1:12 pm
Another post with nothing to work with? Don't you get tired of us asking you to put in the leg work?
Given your description it seems you are over complicating it. If all you have to worry about is the letter A just use replace.
if object_id('tempdb..#Sites') is not null
drop table #Sites
create table #Sites
(
SiteID varchar(8)
)
insert #Sites
select '1000' union all
select '2000' union all
select '3000' union all
select '3000AA' union all
select '4000'
--the above is what you should have posted so we have something to work with.
--Then all we have to do is work on the problem instead of setting it up too.
select cast(replace(SiteID, 'A', '') as int) as NewSiteID
from #Sites
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 1:22 pm
Based on your "Absolutes", here is the logic that you may try:
Declare @SiteID varchar(15)
Set @SiteID = '3000AA'
Select Case when Len(@SiteID)> 4 then
Convert(int, SubString(@SiteID,1,4) + '1')
Else
Convert(int, @SiteID)
End
Set @SiteID = '3000'
Select Case when Len(@SiteID)> 4 then
Convert(int, SubString(@SiteID,1,4) + '1')
Else
Convert(int, @SiteID)
End
January 31, 2013 at 1:29 pm
I missed one of your requirements.
What I can't do is take 3000AA and make it 3001, it needs to be 30001. Maybe a way to declare it in the CTE so I could:
You could do this like this.
select cast(case charindex('A', SiteID) when 0 then SiteID else replace(SiteID, 'A', '') + '1' end as int)
from #Sites
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 1:30 pm
Or maybe another way since you say the data is always AA.
select cast(replace(SiteID, 'AA', '1') as int) as NewSiteID
from #Sites
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 1:58 pm
Thank you for the help. Is this an example of a good post? http://qa.sqlservercentral.com/Forums/Topic1414095-392-1.aspx or are you just picking on me?
I don't understand the need for the table creation. I was simply asking a quesiton for ideas:
If you have references or logic ideas, please feel free to share. I understand that there's not much code here so I'm really looking for spitball ideas versus actual working code.
You're exactly right, all I needed was "cast(replace" and immediately, I knew that was it. Thank you again and I will strive even hard next time to meet your demands!
January 31, 2013 at 2:01 pm
Thank you yu.he. If what Sean posted earlier doesn't work, then I will give this a whirl as it should work as well. Thanks again.
January 31, 2013 at 2:09 pm
SQL_Enthusiast (1/31/2013)
Thank you for the help. Is this an example of a good post? http://qa.sqlservercentral.com/Forums/Topic1414095-392-1.aspx or are you just picking on me?I don't understand the need for the table creation. I was simply asking a quesiton for ideas:
If you have references or logic ideas, please feel free to share. I understand that there's not much code here so I'm really looking for spitball ideas versus actual working code.You're exactly right, all I needed was "cast(replace" and immediately, I knew that was it. Thank you again and I will strive even hard next time to meet your demands!
I would have to say that one you linked is not a shining example of high quality. 😀
The real issue is that many of us (myself included) are visual people and having the table to work with makes it easier to see the issue. I had a vague idea of what would resolve it but until I had it in a table I couldn't really see it. I certainly couldn't test my code. I apologize if I came off as anything other than helpful. I somewhat skimmed your OP for the gist of what you needed. At any rate, the code I posted should meet the requirements, at least following the rules and absolutes you posted. Hope this works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 7:02 pm
Sean Lange (1/31/2013)
The real issue is that many of us (myself included) are visual people and having the table to work with makes it easier to see the issue. I had a vague idea of what would resolve it but until I had it in a table I couldn't really see it. I certainly couldn't test my code. I apologize if I came off as anything other than helpful. I somewhat skimmed your OP for the gist of what you needed. At any rate, the code I posted should meet the requirements, at least following the rules and absolutes you posted. Hope this works for you.
I want to second what Sean said above. The most helpful to me is seeing:
1. DDL for the table(s)
2. Some consumable INSERTs of sample data into that table
3. Expected results based on the sample data.
If you've crafted your sample data properly (and provided accurate[/i] expected results), detailed explanations of the logic are rarely required but some small amount of this may be required.
For me, running the code you provide (adding a SELECT from the source tables) and inspecting the expected results usually leads me to a transformation relatively quickly. Interpreting detailed explanations sometimes seriously slows this down.
And since we're all volunteers here, we like to be able to answer questions in the shortest time commitment possible. Mainly so we can move on and help others.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply