question

jhowe avatar image
jhowe asked

selecting 1st digit

Hi all, How do i do this in SQL? UPDATE table B set customernumber = customernumber table A WHERE (1st digit of customer number = 0,1,2,3 and maximum length = 10)? I've been messing around with substring and len but can't seem to get the results i want... Thanks!
sql-server-2008sql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
What links table A and B? If it is just the customer number, then you will just be updating the value on B with the same value from A. Also the logic you have stated, is that for the customer number in A or B? I would use something like update B set B.customernumber = a.customernumber from tableB B join tableA A on B.somefield = A.somefield where left(1,B.customernumber) in (0,1,2,3) and len(B.customernumber)
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Kev Riley As always, I agree with the points described :) @Jhowe As far as LEFT and Substring performance is concerned, LEFT is internally handled as SUBSTRING (In all the cases I have experienced). You can see it in the execution plans. So the performance should be the same. But as Kev Riley said, LEFT reads better :)
1 Like 1 ·
ah yes, thanks kev! do you always use LEFT or RIGHT in this situation or can you use substring? I think i owe you more than one pint now ;) next sql bits?
0 Likes 0 ·
I don't know of any performance difference between left or substring - its just a personal preference. Right I'll take you up on that offer of a pint, just have to find an event that we are both at!
0 Likes 0 ·
@Jhowe I beleive your pseudo-code "WHERE (1st digit of customer number = 0,1,2,3... is the key factor that Kev Riley have used LEFT function. Otherwise, I am quite confident that he would have used the LIKE operator (something like '[0123]%'), which would perform better than LEFT and SUBSTRING :)
0 Likes 0 ·
@Usman, yes if there is an index on that field. If however this is a quick, one-off, I think the LEFT reads better.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.