CODEWITHSUNDEEP
sql-server
NShyn
NShyn

Reputation: 3

How to select a numeric part of nvarchar datatype field

I want to select the numeric part of data that saved in nvarchar() datatype column in sqlserver. The size of a character in rows doesn't same and maybe some of the rows don't have the numeric part on the column, for example, the data format like /TablePhoneHome>

or 

<TablePhoneHome></TablePhoneHome>

or 

<TablePhoneHome><Number Num="9123159834"/></TablePhoneHome>

or 

<TablePhoneHome><Number Num="somthing"/></TablePhoneHome>

I want to select the phone number from that like :

09151826166-09151150374
null
9123159834

Upvotes: 0

Views: 62

Answers (1)

Thom A
Thom A

Reputation: 95574

Assuming the data type is actually xml (and if it isn't, then you should fix your data type to be xml) you can easily use XQUERY to get the value:

SELECT YT.YourColumn.value('(/TablePhoneHome/Number/@Num)[1]','varchar(50)') AS Num
FROM YourTable YT;

Upvotes: 1

Related Questions