0

Here is my column that displays the full addresses.

enter image description here

I want to split the name street and the number street in two separate columns. I have tried this:

SELECT
   substring([ObjektStr], 1, patindex('%[1-9]%', [ObjektStr])-1) as Street,
   substring([ObjektStr], patindex('%[1-9]%', [ObjektStr]), len([ObjektStr])) as HouseNumber
FROM
   table1

without a result. I want something to search for the name until it finds the first number. Any idea?

Thanks

Nick Zagkanas
  • 89
  • 7
  • Based on *image* of the sample data, why not just get the value after the last whitespace (`' '`)? – Larnu Feb 17 '21 at 11:55
  • How do i do that? – Nick Zagkanas Feb 17 '21 at 11:57
  • [Is there a LastIndexOf in SQL Server?](https://stackoverflow.com/q/39002025/2029983) – Larnu Feb 17 '21 at 11:59
  • I get one error on a spesific address(Falkstr. 107) – Nick Zagkanas Feb 17 '21 at 12:05
  • *"I get one error"* Great, so debug it and fix it if you aren't going to share it. :) – Larnu Feb 17 '21 at 12:07
  • The error is this: "Error converting the varchar value "Falkstr. 107" to the int data type" – Nick Zagkanas Feb 17 '21 at 12:15
  • The error is telling you the problem there. `'Falkstr. 107'` is definately *not* an `int`. But why are you trying to convert the values to an `int` anyway? There are clear examples of house numbers that aren't `int` values. What "int" is `'43-45'` (no, it's not `2`), or `'12a'`? – Larnu Feb 17 '21 at 12:20
  • I know that are not int. My point is that I want to put all the name address of every row until it finds the first number.. should I convert all of this into "int" or "varchar" and then write a query? – Nick Zagkanas Feb 17 '21 at 12:24
  • None of it is an `int`, I don't know why you are converting anything to an `int`. See the accepted answer in the dupe again, it shows you exactly what you need to do. It can't produce the error you have, as it doesn't do any implicit or explicit conversion. – Larnu Feb 17 '21 at 12:26
  • do you have any idea of how could I split the varchar into name address and number address? – Nick Zagkanas Feb 17 '21 at 12:27
  • *"do you have any idea of how could"* Yes, see my commetns above and the linked duplicate. – Larnu Feb 17 '21 at 12:27
  • Yes, I saw your comments and I tried the solution you gave me [link]https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server and I had an error as I wrote it before – Nick Zagkanas Feb 17 '21 at 12:31
  • I [can't replicate](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=38740219c7ed441db6976233f21dd949) your error. – Larnu Feb 17 '21 at 12:41

0 Answers0