Change field with numeric and alphanumeric to numeric

I have a text field with records that mostly contain numbers, but in some of the records the field is alphanumeric, such as P234032. I want to convert the field to numeric, and just allow any alphanumeric fields to be ignored, and become null.

I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?

Thanks,

RandyDoes this help

set nocount on
go
create table #test (col1 char(10), col2 char(10))
go
insert #test values ('BU89090', 'record 1' )
insert #test values ('GHJ9213', 'record 2' )
insert #test values ('79898', 'record 3' )
insert #test values ('89067', 'record 4' )
insert #test values ('09889067', 'record 5' )
go

select *
from #test
go
select convert(int, case when isnumeric(col1) = 1 then col1 else NULL end) as intCol1, col2
from #test
go
drop table #test

Output
col1 col2
---- ----
BU89090 record 1
GHJ9213 record 2
79898 record 3
89067 record 4
09889067 record 5

intCol1 col2
---- ----
NULL record 1
NULL record 2
79898 record 3
89067 record 4
9889067 record 5|||achorozy,

Thanks for the reply! I haven't tried this yet, but will have a chance first thing tomorrow morning, and will let you know. The SQL Server 2000 course that I just completed was pretty thorough, but seems like it didn't cover the nitty gritty things that I need to know right now.

This is sure a different animal than MS Access!

Thanks again,

Randy

Chalenge returning the first available number in a sequence

Hi all,

Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.

Given the client table below my query would return 4 because its the lowest number availeble.

Thanks.

client table

clnum

1

2

3

6

7

Writing a single sql query for this task is challenging. Though I suggest that you could write a stored procedure that loops through a cursor pointing to clnum and check which number isn't present in the column:

psuedo code

open a cusor on clnum

loop i =1 to max int

loop thru the cursor and check if i is present ; if not then return i

end of loop

close the cursor

For help on using loops and cursors,seehttp://www.databasejournal.com/features/mssql/article.php/3111031

Hope this helps.

QI.

|||

Create a temp table or table variable, insert your clientid's and query from it. Something like this:

Declare @.ttable( Idint identity, clientidint)insert into @.tselect 1unionallselect 2unionallselect 3unionallselect 6unionallselect 7select top 1 IDfrom @.tWhere Idnot in (Select clientidfrom @.t)order by id

|||

This would not work if we already had 4 and 5 in the column as well. For example:

Declare @.ttable( Idintidentity, clientidint)

insertinto @.t

select 1unionall

select 2unionall

select 3unionall

select 4unionall

select 5unionall

select 6unionall

select 7

selecttop 1 ID

from @.tWhere Idnotin(Select clientidfrom @.t)

orderby id

The above code wouldn't return anything. Even though it should return 8, which is the lowest available number. Also, you want to be able to select from a column which may have an unknown number of rows so I don't think you can keep on doing unions like this.

QI.

|||

(1) If the ID returned is NULL, you can always query back with MAX(Clientid) and increment by 1. If you write a function to do this, it should be very simple.

(2) There is no need to manually write the INSERTs. That was just for a sample.

INSERT INTO @.T

SELECT Clientid

FROM Clients

would insert all the clients into the table.

catch sql command if value doesnt exist

I have a sql command that is loaded on page load that collects information based on the query string. The query string is a random group of numbers and letters. How do I catch it and direct to an error page if the query can not be found in the database?

Thanks!

if you are trying to get some parameters from the querystring then you can use

dim queryvariable as string =request.querystring("variable")

try

dim sqlquery as string

sqlquery="SELECT column_Name from Table_name where variable= "& queryvariable

//use this sqlquery to check whether it returns some rows or not

catch

response.redirect("pageNotFound.aspx")

end try

|||

I'm pretty sure i did all that.

in page load i'm doing

getUserInfo(Request.QueryString["uid"]);

then the method

protected void getUserInfo(string userid) {string selectCmd ="SELECT * from users WHERE ID = @.id";string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection =new SqlConnection(strConnection); SqlCommand myCommand =new SqlCommand(selectCmd, myConnection); myCommand.Parameters.Add(new SqlParameter("@.id", SqlDbType.VarChar, 10)); myCommand.Parameters["@.id"].Value = userid;try { myConnection.Open(); SqlDataReader datareader = myCommand.ExecuteReader();while (datareader.Read()) { lblFirstName.Text = datareader["firstname"].ToString(); lblLastName.Text = datareader["lastname"].ToString(); lblTeam.Text = datareader["team"].ToString(); lblOffice.Text = datareader["office"].ToString(); } datareader.Close(); myConnection.Close(); }catch { Response.Redirect("~/error.aspx"); }

|||

any ideas?

|||

In your code you aren't checking if the datareader actually contains any data or not, so if no records are being returned nothing happens. 1 simple way to do it is:

1. Declare a boolean variable at the top initialized to False: boolean bolUserFound = False

2. Inside the while loop set the value to true: bolUserFound = True

3. After you close the connection evaluate the variable and if it's still false you know no records were found and you need to redirect to your error page:

if (bolUserFound = False) {

Response.Redirect("~/error.aspx");

}

|||

perfect!

Exactly what i needed...