any SQL experts??

[7cent]

I am losing my mind on this one.. I need to strip the first 3 characters of a value and then add that number into a new column..

sorry, if it's vague, not good at articulating my sql problems..

any help would be appreciated..

thanks

[BCH]

So In other words, the value in column A is 123456789 you need a script that will put 456789 in another column in the same row? This sounds like SQL homework as I can't think of a practical use for such a thing except perhaps stripping area codes from phone numbers.

[7cent]

So In other words, the value in column A is 123456789 you need a script that will put 456789 in another column in the same row? This sounds like SQL homework as I can't think of a practical use for such a thing except perhaps stripping area codes from phone numbers.


Yes, exactly.. What I am doing is trying to link my part numbers with another companies part numbers. In their system they give a vendor indicator in the beginning of the actual part number. My data has a column for the vendor, and then the part number..

unfortunately it is not as easy as taking right(number,7) because the part numbers will range from 2 to 5 digits.. The phone number example is easy because that is constant.

[BCH]

Crap. That is a toughie. Because math doesn't work that way. I had a problem like this with IBM and their stupid Magnetic Swipe Access cards in an Access control system I programmed for the owner of a building once... Their card numbers all had one of maybe 3 or 4 4-digit prefixes before the actual card number... Now I have to remember back to 2001....

[BCH]

You could query for the number of digits and then just subtract the vendor number with that many zeroes. This might be what I did There's a "length" or "len" function is there not?

[BCH]

Oh Wait, but you want to retain the vendor number and there's more than one... hmmmmmm.

[blazin]

You're going to want to use the char_length expression to determine the number of characters, then subtract 3, then take the right() of that value

RIGHT(expression,(CHAR_LENGTH(expression)-3))

[7cent]

I think I got it.. It looks like it is either 7 thru 9 digits after the 3 letter prefix. I am going to take the first 9 and dump that in a column.. then take the first 8 where number like 's%' or 'c%', then first 7 from that.. then combine it all into 1 column..



Thanks... I will probaby have some more, because that is 1 out of 50 vendors.. Ahh, it's going to be a great weekend..

thanks again.

[BCH]

You're going to want to use the char_length expression to determine the number of characters, then subtract 3, then take the right() of that value

RIGHT(expression,(CHAR_LENGTH(expression)-3))

Oh yeah the result of the length function minus the number of digits you want to remove on the left... This makes the most sense.

[7cent]

Oh yeah the result of the length function minus the number of digits you want to remove on the left... This makes the most sense.

that looks good, but I'm getting

Server: Msg 195, Level 15, State 10, Line 1
'CHAR_LENGTH' is not a recognized function name.

googling the error now

[blazin]

Function: char_length(string) or character_length(string)
Return Type: integer
Description: length of string
Example: char_length('jose')
Result: 4

try character_length

[7cent]

same..It looks to be a sql 2000 problem..

[blazin]

try LENGTH(str)

[7cent]

Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.


close... length didn't work but LEN did.. then I got this error

[Special Eddie]

If you need any help with BASIC or DOS commands, give me a call.:D

[BCH]

Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.


close... length didn't work but LEN did.. then I got this error

Fixed : SQL results pane text always looks better in code tags :)

What version of SQL anyway?? 7? 2005?

[7cent]

2000... I had 2005 installed but I do not have access to the servers I need, so I can't use it..

but first go, worked.. Now I just need to dig through all of the vendors.