Update if Right Charater is not an Alpha Character

[RJL1]

Hello

I'm working on a stored procedure to update a field with an Alpha Character (e.g. X) at the end. I want to only update if the right character of the field in not already a letter (alpha character). I do not want to end up with multiple letters at the end of the number (e.g ##########XXX)

Right now I have it hardoced to X but the end user could select another letter to update the number with.

Is there a way to check insted of != 'X' have it check where != A-Z?


Here is my code so far

UPDATE SHIPPING_CONTAINER SET
SHIPPING_CONTAINER.CONTAINER_ID = SHIPPING_CONTAINER.CONTAINER_ID + @CHARACTER
WHERE
RIGHT(SHIPPING_CONTAINER.CONTAINER_ID,1) != 'X'
AND SHIPPING_CONTAINER.STATUS = 600
AND LEN(SHIPPING_CONTAINER.CONTAINER_ID) < 25
AND SHIPPING_CONTAINER.INTERNAL_SHIPMENT_NUM IN
(SELECT SHIPMENT_HEADER.INTERNAL_SHIPMENT_NUM
   FROM SHIPMENT_HEADER
  WHERE SHIPMENT_HEADER.SHIPMENT_ID = @SHIPMENT_ID)

Thanks in advance

 

[gmmastros]

You can use a like comparison for this.

Ex:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('')
Insert Into @Temp Values('1')
Insert Into @Temp Values('24234')
Insert Into @Temp Values('A')
Insert Into @Temp Values('X')
Insert Into @Temp Values('23984F')


Select * From @Temp Where Data Like '%[^A-Z]'

** Note that the NULL and empty string are also ignored.

You can also use the between operator, like this:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('')
Insert Into @Temp Values('1')
Insert Into @Temp Values('24234')
Insert Into @Temp Values('A')
Insert Into @Temp Values('X')
Insert Into @Temp Values('23984F')

Select * From @Temp Where Right(Data, 1) Not Between 'A' and 'Z'

** Note that NULL is ignored but the empty string is returned.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh... This also works.

Select * From @Temp Where Data Not Like '%[A-Z]'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom