String parsing in SQL Server

Question

I have string column email_id; the data will look like this:

email_id 
"1"
"6"
"3 4"
"8"
"0 3"
"0 5 7"

I want to get list of ids as integer. If I have two numbers in my string, I want the last one. My result should look like;

SELECT some_function (email_id ) FROM table
1
6
4
8
3
7

Is it possible to do this in SQL Server?

You haven't explained why "3 4" is parsed as 4 and "0 3" as 3. Are these space-delimited lists where you always take the last integer? If so, CHARINDEX and SUBSTRING can help you. If not, you need to explain your logic. — Pondlife
– Pondlife, Commented Dec 9, 2011 at 10:15
This is not the correct way to represent a one to many relationship. You should have a separate table with one row for each entityId, emailId — Martin Smith
– Martin Smith, Commented Dec 9, 2011 at 10:23

MatBailie · Accepted Answer · 2011-12-09 10:26:15Z

5

SELECT
  CAST(RIGHT(email_id, LEN(email_id) - CHARINDEX(' ', email_id)) AS INT)
FROM
  yourTable

IF and ONLY IF, all your values can reliably be cast to an INT, and there is only ever one space at most.

EDIT To deal with a list of n values

This isn't pretty, but it avoid recurrsion and/or loops. If someone gives an answer without REVERSE() test to see if it's faster than this or not.

SELECT
  CAST(
    REVERSE(
      LEFT(
        REVERSE(email_id),
        CHARINDEX(' ', REVERSE(email_id) + ' ') - 1
      )
    )
    AS INT
  )
FROM
  yourTable

edited Dec 9, 2011 at 10:26

answered Dec 9, 2011 at 10:15

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bala Over a year ago

This is working if i have only two values. Anyway it was helpful, Now I'mtrying to edit your SQL to make it work for more than two numbers...thx

MatBailie Over a year ago

@Ba1a - Updated using REVERSE() and LEFT() to take the last item, regardless of how many items there are.

Bala Over a year ago

select CAST(REVERSE (LEFT ( REVERSE ('3 4 15'), CHARINDEX(' ', REVERSE ('3 4 15')))) AS INT) This itself working fine...I didn't understand why did you add space in the reversed email-id.

MatBailie Over a year ago

The + ' ' is needed for when there is no space in the string (You'll get CAST('' AS INT) otherwise). And the - 1 is there for 'purity sake' to avoid having a space in the string being CAST to an INT.

Bala Over a year ago

Ok. I was handling single number by CASE statement like ; SELECT CASE WHEN LEN('3 4 15')> 1 THEN CAST(REVERSE (LEFT ( REVERSE ('3 4 15'), CHARINDEX(' ', REVERSE ('3 4 15')))) AS INT) ELSE CAST('3 4 15' AS INT) END

|

marc_s · Accepted Answer · 2011-12-09 10:27:30Z

-2

SELECT CAST(replace(your_column ,' ','') as int)  FROM table

edited Dec 9, 2011 at 10:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 9, 2011 at 10:13

Dezigo

3,2563 gold badges34 silver badges39 bronze badges

5 Comments

Bala Over a year ago

select CAST('3 4' AS INT) -- This row will fail ..I need to get 4 as value for this row

MatBailie Over a year ago

@Dezigo - And for the records that have multiple IDs separated by a space?

Pondlife Over a year ago

This will fail on values like "3 4"

Dezigo Over a year ago

u need to add replace function

Dezigo Over a year ago

Dems - oh. OK. I have missed it.(

Collectives™ on Stack Overflow

String parsing in SQL Server

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related