Character fields with numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Character fields with numbers

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-29 : 14:25:25

I am working on the database for our ERP system. The field name is fclot and the type is Char 20, but most records contain 6 digit numbers.

If I wanted to search for records that are numeric and are greater than 300000, how would I do so?

I tried using ISNUMERIC but this doesn't seem to have the same functionality in SQL as it does in Visual Basic.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 14:35:33

this seems to work in sql 2005:


create table #junk (fclot char(20))
insert #junk 
select 'TGTGTGTGTG' union all
select '12' union all
select '300001' union all
select '   dks         '


select * from #junk where isNumeric(fclot)=1 and fclot > 300000

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-29 : 14:56:04

But ISNUMERIC is unreliable
create table #junk (fclot char(20))
insert #junk
select 'TGTGTGTGTG' union all
select '12' union all
select '300001' union all
select '3e2' union all
select ' dks '

select * from #junk where isNumeric(fclot)=1 and fclot > 300000

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 14:59:07

true - but OP said, "..but most records contain 6 digit numbers"

but feel free to post something more usable

Be One with the Optimizer
TG

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-29 : 15:03:08

Thank you TG, that makes sense.

I'm trying to update the SQL tables manually rather than use our ERP system to generate certain records such as serial numbers. I was going to use a looping statement but I'd guess that there is a better set-based solution.

If I wanted to construct an insert statement such as:

INSERT INTO dbo.qalotc (
	fctype,
	fcdoc,
	fcpartno,
	fcpartrev,
	fclot,
	fcuseintype,
	fnquantity,
	fcmeasure,
	fac,
	fcudrev
) VALUES ( 
	'J',
	'IX652-0000',
	'CI 5500',
	'B',
	'300000',
	'I',
	1,
	'EA',
	'Default',
	'B'
	)

and this needed to be inserted 100 times each time incrementing the fclot (300001, 300002 etc) , what is the best way to do that?

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-07-29 : 15:12:49

One think you can do is do a REPLACE for characters that ISNUMERIC fails on.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-29 : 15:16:11

Thank you for your response, but that last post of mine was really another issue. I need to insert large quantities of Serial Numbers and our ERP system is too cumbersome to do it.

So, I really would be inserting new records. I'm guessing that there is a set based way of doing that rather than a While loop which would add 1 to the previous serial (fclot) and do another insert.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 15:19:30

Does fclot have to be char(20)? Doesn't make sense for what seems like integer data.

Regarding a set based approach to add 100 rows you could use a "numbers" table. ie:
Rather than INSERT..VALUES()
perhaps:

insert <table>(colLIst)
select 'J',
'IX652-0000',
'CI 5500',
'B',
v.number + 100000 ,
'I',
1,
'EA',
'Default',
'B'
from master..spt_values v
where type = 'p'
and number > 0
and number < 101

Be One with the Optimizer
TG

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-29 : 15:24:34

Unfortunately, I am stuck with the table structure as it is. I believe it is that way so you can use serials that include letters. However, we do not use it that way.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-29 : 15:41:56

quote:
true - but OP said, "..but most records contain 6 digit numbers"

but feel free to post something more usable

I was going to but couldn't get it to work!

something like
select * from #junk
where
fclot not like '%[a-z]%'
and isNumeric(fclot)=1
and fclot > 300000
but this still gives the fail to convert to int error, even tho the first 2 lines of the where statement returns jsut the numerics
Jim

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-29 : 16:25:47

If I'm understanding correctly I created a table called SerialNumbers. This is my insert statement:

INSERT INTO dbo.qalotc (
	fctype,
	fcdoc,
	fcpartno,
	fcpartrev,
	fclot,
	fcuseintype,
	fnquantity,
	fcmeasure,
	fac,
	fcudrev
) VALUES ( SELECT  
	'J',
	'IX652-0000',
	'CI 5500',
	'B',
	S.serial,
	'I',
	1,
	'EA',
	'Default',
	'B'
	FROM serialnumbers S WHERE serial>300000 AND serial <300100
	)

The select statement works on it's own. If I run the preceeding statement I get:

Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near ')'.

What am I doing wrong?

Once again, thanks for the help.

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-29 : 17:57:01

Ah, this worked:

I removed the "VALUES" and cast the serial number as a character.

INSERT INTO dbo.qalotc (
	fctype,
	fcdoc,
	fcpartno,
	fcpartrev,
	fclot,
	fcuseintype,
	fnquantity,
	fcmeasure,
	fac,
	fcudrev
) (
	SELECT  
	'J',
	'IX652-0000',
	'CI 5500',
	'B',
	CAST(S.serial AS CHAR),
	'I',
	1,
	'EA',
	'Default',
	'B'
	FROM serialnumbers S WHERE serial>300000 AND serial <300100)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 00:07:39

quote:
Originally posted by DavidChel

I am working on the database for our ERP system. The field name is fclot and the type is Char 20, but most records contain 6 digit numbers.

If I wanted to search for records that are numeric and are greater than 300000, how would I do so?

I tried using ISNUMERIC but this doesn't seem to have the same functionality in SQL as it does in Visual Basic.

you can use enhanced isnumeric function given in below link

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-30 : 10:06:13

Thanks all, so now I noticed that the ERP system does something unusual when adding these serials. Through testing I've determined that the fcudrev column contains the same value as the fcpartrev but only for the first serial entered for that fcdoc (which is a Job Order Number). So, the input would look like:

fcdoc       fcpartrev    fclot    fcudrev
IX652-0000  B            300000   B
IX652-0000  B            300001   
IX652-0000  B            300002

Would the best way to handle this be with 2 queries such as this:

INSERT INTO dbo.qalotc (
	fctype,
	fcdoc,
	fcpartno,
	fcpartrev,
	fclot,
	fcuseintype,
	fnquantity,
	fcmeasure,
	fac,
	fcudrev
) VALUES (
	'J',
	'IX652-0000',
	'CI 5500',
	'B',
	'300000',
	'I',
	1,
	'EA',
	'Default',
	'B'
	)

INSERT INTO dbo.qalotc (
	fctype,
	fcdoc,
	fcpartno,
	fcpartrev,
	fclot,
	fcuseintype,
	fnquantity,
	fcmeasure,
	fac,
	fcudrev
) (
	SELECT  
	'J',
	'IX652-0000',
	'CI 5500',
	'B',
	CAST(S.serial AS CHAR),
	'I',
	1,
	'EA',
	'Default',
	''
	FROM serialnumbers S WHERE serial>300001 AND serial <300101)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 10:12:21

quote:
Originally posted by DavidChel

Thanks all, so now I noticed that the ERP system does something unusual when adding these serials. Through testing I've determined that the fcudrev column contains the same value as the fcpartrev but only for the first serial entered for that fcdoc (which is a Job Order Number). So, the input would look like:

fcdoc fcpartrev fclot fcudrev
IX652-0000 B 300000 B
IX652-0000 B 300001
IX652-0000 B 300002

Would the best way to handle this be with 2 queries such as this:

INSERT INTO dbo.qalotc (
fctype,
fcdoc,
fcpartno,
fcpartrev,
fclot,
fcuseintype,
fnquantity,
fcmeasure,
fac,
fcudrev
) VALUES (
'J',
'IX652-0000',
'CI 5500',
'B',
'300000',
'I',
1,
'EA',
'Default',
'B'
)

INSERT INTO dbo.qalotc (
fctype,
fcdoc,
fcpartno,
fcpartrev,
fclot,
fcuseintype,
fnquantity,
fcmeasure,
fac,
fcudrev
) (
SELECT
'J',
'IX652-0000',
'CI 5500',
'B',
CAST(S.serial AS CHAR),
'I',
1,
'EA',
'Default',
''
FROM serialnumbers S WHERE serial>300001 AND serial <300101)

are you trying to do this manually for each of the fcdoc group? Also are you sure that this trend will be followed strictly in all of fcdoc groups (first one with serial number and rest with blank value) ?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-30 : 12:39:29

I'm investigating more thoroughly, but yes this seems to be the pattern. The user would enter the first serial number required (fclot), the job number (fcdoc), and the number of serials to generate.

Assuming they needed 100, those are the queries that would enter in the correct info with the first record being inserted with the fcudrev having a value, and each subsequent serial (fclot) having a fcudrev being assigned a blank string.

Is there a better way to do this with one statement, rather than 2?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:10:54

[code]INSERT INTO dbo.qalotc (
fctype,
fcdoc,
fcpartno,
fcpartrev,
fclot,
fcuseintype,
fnquantity,
fcmeasure,
fac,
fcudrev
)
SELECT
'J',
'IX652-0000',
'CI 5500',
'B',
S.serial + v.number,
'I',
1,
'EA',
'Default',
CASE WHEN v.number=0 THEN 'B' ELSE '' END
FROM serialnumbers S
CROSS JOIN master..spt_values v
WHERE serial=@Serial
AND v.number<=@Number
AND v.type='p'[/code]

@serial is serial number given by user and @Number is number of serial numbers to be generated

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-30 : 16:05:56

quote:
Originally posted by visakh16

CASE WHEN v.number=0 THEN 'B' ELSE '' END
FROM serialnumbers S
CROSS JOIN master..spt_values v
WHERE serial=@Serial
AND v.number<=@Number
AND v.type='p'

@serial is serial number given by user and @Number is number of serial numbers to be generated

Thanks for your help. I think I get it. However, I don't understand why you're using a cross join. And what's with the v.type='p'?

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-07-31 : 15:03:39

bump. Anybody? :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources