Sql - case

Other SQL Server Topics
1

Hi,

I'm not sure what the best way to do this and would like some help if possible please.

Below is the SQL Query I'm trying to achieve:

SELECT
'+44' + right(replace(replace(Number,' ',''),'+',''),10) as Number2,
CASE
    WHEN Number2 LIKE '+447%' THEN 'MOBILE'
    ELSE 'PHONE OR INVALID'
END AS NumberDefine
FROM sql_server_test_a;

I'm dealing with a DB that is a complete mess, theres numbers that start with 07, 00447, +447 and what I'm doing is removing all spaces, removing the + sign and then grabbing the last 10 numbers which should be a UK number. I'm then adding +44 in front of it and then doing a case to define if its a Mobile Number or a Telephone number.

Obviously that isn't going to work as 'Number2' does not exist in the DB.

How is the best way to achieve what I'm trying to do?

Any help would be much appreciated.

Thank you.

2

For a start you need to understand the logical order in which SQL clauses are processed:
https://blog.sqlauthority.com/2020/11/18/sql-server-logical-processing-order-of-the-select-statement/

You could then try something like:

SELECT T.*
	,X.Number2
	,CASE
		WHEN X.Number2 LIKE '+447%'
		THEN 'MOBILE'
		ELSE 'PHONE OR INVALID'
	END AS NumberDefine
FROM sql_server_test_a T
	CROSS APPLY
	(
		VALUES
		(
			'+44' + RIGHT(REPLACE(REPLACE(T.Number, ' ', ''), '+', ''), 10)
		)
	) X (Number2);