0

i have a table city and columns name , id and address.

name id address
Hamburg 05088 45478, AachenerStr 128 num. +49(xxx)xxxxxxx

My question is how can i select only numbers from column address and copy it to new column numbers? like this:

name id address numbers
Hamburg 05088 45478, AachenerStr 128 num. +49(xxx)xxxxxxx +49(xxx)xxxxxxx
firstimeinsql
  • 1
  • 1
  • 1
    You Mean you want to extract Mobile Number ? – Roshan Nuvvula Apr 18 '22 at 07:31
  • @Roshan i want to keep mobile number in address as well – firstimeinsql Apr 18 '22 at 07:32
  • 2
    Does this answer your question? [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Patrick Artner Apr 18 '22 at 07:33
  • 1
    [string_split](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15) – Patrick Artner Apr 18 '22 at 07:34
  • @PatrickArtner not sure. Because some address column has extra info like building numbers etc. – firstimeinsql Apr 18 '22 at 07:39
  • 3
    @first - well if some addressen contain the eyecolor of the inhabitants ... don't YOU think that would be worthwhile to be mentioned in your "demo data set of 1 line" ? You may want to add a comprehensive example of the data you are handling. Any solution proposed on missing information by you will always be lacking ;) – Patrick Artner Apr 18 '22 at 07:41
  • 2
    Does your sample data truly represent your requirements? Your new columns is "numbers" - plural, so could there be more than one? Are numbers always formatted with a `+` prefix? Are numbers always last in the address? Can a `+` appear where it's not a number prefix? etc – Stu Apr 18 '22 at 07:42
  • 1
    Does this answer your question? [Regex usage to create new column in Sql](https://stackoverflow.com/questions/42145017/regex-usage-to-create-new-column-in-sql) - or [using-regex-in-sql-server](https://stackoverflow.com/a/58460263/7505395)you may need to find a regex that captures 95+% of your given telefone numbers which is not trivial: `+497611234567 +49(761)1234567 +49-761-1234567 +49 761 123 45 67` etc – Patrick Artner Apr 18 '22 at 07:43

1 Answers1

0

  1. If the Length of the Mobile Number is fixed and Always last in position you can use

    Select RIGHT(address,15)

  2. Extract using the + Symbol.

    declare @address varchar(1000) = '45478, AachenerStr 128 num. +49(xxx)xxxxxxx'
select substring(@address,CHARINDEX('+',@address),len(@address))
Roshan Nuvvula
  • 803
  • 1
  • 8
  • 28