Newbie SQL Question 2

[aumstu]

I need to pull out data from sql and some of the data will need to be change. I am going to pull the data out first using DTS packages and then i want to write some code to change the rest of the data. I have seen some If statements mentioned in this forum...but are these stored procedures or do you just type them in like regular sql? also, If I wanted to loop through a file...is there a while till end of file? Thanks for any help...

 

[jbenson001]

It doesn't seem like you need a DTS package. You can write you sql in query analyzer. Any code you do in a stored procedure you can do there. As for looping, you use:

While <some condition>
Begin
  ... code here ...
End

Loops are a last resort. You can usually use a set based operation to do what you need. If you provide more details and some sample data, before and after, we can help you better.

Jim

 

[aumstu]

Thanks for answering

This may not be the most efficient way to get the data but this is what I was thinking. I was going to pull the name, age, etc. from the database using a dts package and then add a stored procedure to do some clean up. I am hoping to click on one button and let sql server do the rest. One part of the clean up is converting a number (0-9) into a different number (number between 0-9). This is why I was hoping for the while statement. I put some code below of how I would do this in vbscript but I don’t know how to write this in t-sql.

Do while not rs.eof

    If Mid (number, 1, 1) < 5 Then
        Mid (number, 1, 1) = Mid (number, 1, 1) - 4
    Else
        Mid (number, 1, 1) = Mid (number, 1, 1) + 6
    End If

Loop

Does anyone know how to do this in a stored procedure? Plus, is there a better way to do this instead of using a dts package? If so, what is the best way?

Thanks for your help

 

[jbenson001]

How about some sample data before and after your code runs. This will help us to point you in the right direction.

 

[aumstu]

Thanks for answering...I also have to convert the date to elimenate the hypens

Before

ID	ID		  NAME	   Major      StartDate
1	999999999	Billy	  IS	     2005-05-05
2	111111111	Sue        FA	     2004-05-05
3	555555555	John	   AC	     2003-05-05

After

ID	ID		  NAME	     Major    StartDate
1	555555555	Billy	    IS	     20050505
2	777777777	Sue	      FA       20040505
3	111111111	John	     AC	     20030505

Thanks again

 

[SQLDenis]

select replace(startdate,'-','')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

SQLDenis nailed the StartDate issue. But what is the criteria for converting your ID column? Is this column unique(have an index or is the key)

 

[vongrunt]

> One part of the clean up is converting a number (0-9) into a different number (number between 0-9).

Based on sample data, the following rule looks valid

for each digit in ID
If digit < 5, digit = digit + 6 (mod 10)
If digit >= 5, digit = digit -4

Is that true? And that second ID column (?!), is it integer or varchar?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[markgrizzle]

Hi:
I'm new to sql server too (moving from access). I used a cursor to do something similar. I haven't checked this for errors...

Declare@NewNum int
Declare @Num int
Declare val_cursor Cursor
Local
Forward_Only Static For
Select ...
From...

Open val_cursor
Fetch Next From val_cursor Into @Num

While @@Fetch_Status = 0
Begin
Begin
If Cast(Left(@Num, 1) as int) < 5
Set @NewNum = Cast(Left(@Num,1) as int) -4
Else
Set @NewNum = Cast(Left(@Num,1) as int) + 6
End

Set @NewNum = @NewNum + SubString(@Num,2)
Fetch Next From val_cursor into @Num
Continue

End

Close val_cursor
Deallocate val_cursor

Please note, you can't nest cursors and still use @@Fetch_Status to test for the end of the cursor, since it would report on the inner cursor, and may give you the wrong result. In such a case you'd need to keep track of your position in the cursor yourself, or a temp table, etc.

Hope this helps,
Mark

 

[jbenson001]

If you are going to loop throug each digit in the colum, use a While... loop instead of cursors.

 

[SQLDenis]

I don't get how this is going to work?

if the value = 11111
you want to subtract -4 from the first digit what would the result be in this case?

take a look at this example

create table FubaredBeyondBelief (value varchar(50))
insert into FubaredBeyondBelief
select '44444' union all
select '111444' union all
select '2222444' union all
select '6664444' union all
select '8884444' union all
select '9994444' 

select case when left(value,1) < 5 then convert(char(1),left(value,1)-4)  + convert(varchar,(right(value,len(value)-1)))
else convert(char(1),left(value,1)+6)  + convert(varchar,(right(value,len(value)-1))) end, * 
from FubaredBeyondBelief

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

never mind I did not see the sample ID data

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks everyone for answering…


The Second ID column is unique but it is not the primary key.

The Second ID is a varchar column.

I should had explained the whole thing better about the conversion of the second ID. There is actually different if statements for each number…I just added the first one for simplicity. I added a couple of more just to show you what I was doing…but I have not included all of them.

    If Mid(SSN, 1, 1) < 5 Then
        Mid(SSN, 1, 1) = Mid(SSN, 1, 1) - 4
    Else
        Mid(SSN, 1, 1) = Mid(SSN, 1, 1) + 6
    End If
    If Mid(SSN, 2, 1) < 6 Then
        Mid(SSN, 2, 1) = Mid(SSN, 2, 1) + 5
    Else
        Mid(SSN, 2, 1) = Mid(SSN, 2, 1) - 9
    End If
    If Mid(SSN, 3, 1) < 9 Then
        Mid(SSN, 3, 1) = Mid(SSN, 3, 1) + 1
    Else
        Mid(SSN, 3, 1) = Mid(SSN, 3, 1) - 8
End If

Thanks again

 

[vongrunt]

So if first digit equals 2, it should become... -2? ASCII 46? 10-2=8?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[aumstu]

I just threw in numbers for the enctyption...but the real code does not include negative numbers. it would be something like if greater than 5 minus 3 or else plus + 5...Sorry for the confusion

 

[vongrunt]

This is relatively easy to do - with user-defined function and simple conversion "matrix". But plz we need 2-3 exact rules. If that isn't some kind of business secret of course.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[aumstu]

Nah it is not a business secert...

Here is the first 4..it goest through 9

If Mid(SSN, 1, 1) < 5 Then
        Mid(SSN, 1, 1) = Mid(SSN, 1, 1) - 3
    Else
        Mid(SSN, 1, 1) = Mid(SSN, 1, 1) + 5
    End If
    If Mid(SSN, 2, 1) < 2 Then
        Mid(SSN, 2, 1) = Mid(SSN, 2, 1) - 1
    Else
        Mid(SSN, 2, 1) = Mid(SSN, 2, 1) + 7
    End If
    If Mid(SSN, 3, 1) < 8 Then
        Mid(SSN, 3, 1) = Mid(SSN, 3, 1) + 2
    Else
        Mid(SSN, 3, 1) = Mid(SSN, 3, 1) - 6
   End If
 If Mid(SSN, 3, 1) < 4 Then
        Mid(SSN, 3, 1) = Mid(SSN, 3, 1) + 1
    Else
        Mid(SSN, 3, 1) = Mid(SSN, 3, 1) - 3
   End If

 

[vongrunt]

Here is code; you fill in conversion table with correct values

-- replace somename with name of your choice
create function fn_somename( @SSN char(9) )
returns char(9) as
begin
	declare @ret varchar(9); set @ret = @SSN
	declare @digit tinyint

	select @digit = convert(int, substring(@SSN, T.digit, 1)), 
		@ret = stuff(@ret, T.digit, 1, @digit + case when @digit < T.margin then T.value1 else T.value2 end)
		from 
	(	select 1 as digit, 4 as margin, -3 as value1, -5 as value2 union all -- first four rules
		select 2, 2, -1, 7 union all
		select 3, 8,  2,-6 union all
		select 4, 4,  1,-3 union all 
		select 5, 4, -3, 5 union all --from this point on I faked rules. You write the right ones.
		select 6, 2, -1, 7 union all
		select 7, 8,  2,-6 union all
		select 8, 4,  1,-3 union all 
		select 9, 4,  1,-3
	) T

	return @ret
end
go

And usage:

select dbo.fn_somename( '666666666' )

Should work if input is always 9 digits long...

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[aumstu]

that is awesome...thanks so much...I updated the rows using

update sdbache
set column_name = dbo.functionname(column_Name)

thanks again