For Loop, While Loop to Manipulate Data 1

[eselhassan]

My table has the field (PID varchar 17) either 13 or 17 character records. I would like to use the logic below to construct a new table with two fields (CODE varchar 13) and (CODEKEY varchar 17)

IF len(PID) = 13 Then insert into iTable
ESLE
IF len(PID) = 17 Then
1. insert the first 9 characters [iFisrt9]
2. subtract the next 4 characters [iNext4] from the last 4 characters [iLastt4] to get a iCount
3. increment [iFirst9]+[[iNext4] by 1 based of the iCount.
4. insert into same iTable

Example: The record in source table
PID
06029202400250029

Resulting record in new table is
CODE CODEKEY
0602920240025 06029202400250029
0602920240026 06029202400250029
0602920240027 06029202400250029
0602920240028 06029202400250029
0602920240029 06029202400250029

Thanks much for any code to start with

 

[ca8msm]

Firstly, you won't need a "loop"; using an insert statement based on a select statement will be fine.

Secondly, have a look at the CASE expression which will do what you need. Try it out yourself and post back if you run into any issues using it.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

I am too lazy to write this at this moment (one of my new years resolutions, point and hint but do not write for people)

Here is a hint
Use a number/auxiliary table


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[djj55]

Hello Denis,
Because I get most of my answers from other peoples questions,
I like "point and hint" as they make me think. Even though I try to understand provided code, some times I have no idea what is trying to be done.

Happy New Year to those that are on the Gregorian Calendar, for those not on it have a good day. I am just glad the days have started getting longer here.

djj

 

[eselhassan]

Thanks ca8msm,

I'm trying to construct the select statement first. Here is what I have so far:

select CODE =
case
when len(PID) = 17 then left(PID,9) substring(PID,10,4)
else PID
end
from mySourceTable

My question is how do I proceed further with the code to be able to increment by and insert. Please note I'm dealing with varchar data type. Meaning I can not do this: substring(PID,10,4) - right(PID,4) to get my iCount. Please refer to my first post for what I'm trying to accomplish. Thanks for the help.

 

[ca8msm]

Please note I'm dealing with varchar data type. Meaning I can not do this: substring(PID,10,4) - right(PID,4) to get my iCount

Can't you just cast the 4 characters to an integer and then add them together?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

use the *1 trick

declare @v varchar(24)
select @v ='06029202400250029'

select right(@v,4) * 1 -substring(@v,10,4)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[eselhassan]

select cast(substring(PID,10,4)-right(PID,4) as int)
returns an invalid operator for data type error. I'm going to try SQLDenis suggestion shortly. Thanks for the help.

 

[SQLDenis]

>> select cast(substring(PID,10,4)-right(PID,4) as int)

that is of course incorrect

you have to cast both values and also switch the order

example

declare @v varchar(24)
select @v ='06029202400250029'

select cast(right(@v,4) as int) - cast(substring(@v,10,4) as int)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[djj55]

What about the intervening numbers that eselhassan asked for
(26, 27, 28) ? Would you not need a loop for these?

 

[SQLDenis]

You can use a number table and then you can join that table on startnumber and (between start and end number)
since I am lazy I won't write it but here is an example with dates

http://sqlservercode.blogspot.com/2005/09/fast-date-ranges-without-loops-in-sql.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions