Remove first character and remove spaces at the same time..?

1

Morning all,

I'm a little confused here, i'm trying to remove the first character of a phone number and remove any spaces in the phone number, this works fine:

DECLARE @Number nvarchar(15) = '01234 123456'

print (REPLACE((RIGHT(@number, LEN(@number) - 1)), ' ', ''))

As expected I get the output "1234123456"

How ever, when I try to combine that is something like this:

DECLARE @Number nvarchar(15) = '441234123456'

SELECT Emp_ID 
FROM Employees WHERE 
	(
	Employees.E_Status = 'Live' 
	Or 
	Employees.E_Status = 'Past'
	)
	AND 
	(REPLACE
		(RIGHT(Employees.E_Mobile, LEN(Employees.E_Mobile) - 1) , ' ', '')
	) 
	Like '%' + (RIGHT('@Number', LEN('@Number') - 2))

If fails with 'Invalid length parameter passed to the RIGHT function.'
I'm a little confused to what I'm doing wrong...?

Thanks

Dave

2

Doh.....! Not to worry eventually saw my own mistake.... sorry guys.....

DECLARE @Number nvarchar(15) = '441234123456'

SELECT Emp_ID 
FROM Employees WHERE 
	(
	Employees.E_Status = 'Live' 
	Or 
	Employees.E_Status = 'Past'
	)
	AND 
	(REPLACE(Employees.E_Mobile, ' ', '')
	) 
	Like '%' + (RIGHT(@Number, LEN(@Number) - 2))
3

Its going to be slow ... maybe that's not a problem though (e.g. one-off or small data set (including in the future!!)

Storing the Employees.E_Mobile with the space already removed (in a separate field, if you need the current "human readable form" as well :slight_smile: ) would be quicker.

If the job at hand is always to ignore the country-code prefix then either separate that into two fields, or if there is also a need to compare "trailing matches" then perhaps store the REVERSE value? and compare that. Using LIKE 'xxx' + '%' is much faster (if indexes available) than LIKE '%' + 'xxx' (but i'm sure you know all that ...)