any SQL experts??

7cent

Registered User
Dec 5, 2004
2,705
3
443
somewhere, ca
#1
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

Doesn't need your acknowledgement on Twitter
Wackbag Staff
Jun 9, 2005
9,521
236
578
New York
#2
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

Registered User
Dec 5, 2004
2,705
3
443
somewhere, ca
#3
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

Doesn't need your acknowledgement on Twitter
Wackbag Staff
Jun 9, 2005
9,521
236
578
New York
#4
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

Doesn't need your acknowledgement on Twitter
Wackbag Staff
Jun 9, 2005
9,521
236
578
New York
#5
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

Doesn't need your acknowledgement on Twitter
Wackbag Staff
Jun 9, 2005
9,521
236
578
New York
#6
Oh Wait, but you want to retain the vendor number and there's more than one... hmmmmmm.
 

blazin

Registered User
Dec 9, 2004
3,933
436
648
#7
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

Registered User
Dec 5, 2004
2,705
3
443
somewhere, ca
#8
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

Doesn't need your acknowledgement on Twitter
Wackbag Staff
Jun 9, 2005
9,521
236
578
New York
#9
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

Registered User
Dec 5, 2004
2,705
3
443
somewhere, ca
#10
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

Registered User
Dec 9, 2004
3,933
436
648
#11
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

Registered User
Dec 5, 2004
2,705
3
443
somewhere, ca
#14
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
 

BCH

Doesn't need your acknowledgement on Twitter
Wackbag Staff
Jun 9, 2005
9,521
236
578
New York
#16
Code:
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

Registered User
Dec 5, 2004
2,705
3
443
somewhere, ca
#17
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.