Seperating data in a field...

I’m relatively new to SQL, and have a project here at work that I need to get done. I have a table, with a field named temp_link_number, in the format of “0017899162|0019043116|0019043116”. What I need to do is separate these into different entries, so the rest of the data in the record is the same, for all 3 entries, but that number will be unique in each new entry (and in a different field of just "link_number".

Does anyone have any suggestions of what code I can use to select the first 10 digits, the 2nd 10, and 3rd ten? Which are all separated by a |? (the | should not show up in any of the new entries). They are all 10 digits as well, if something is easier using that….. Any tips are greatly appreciated!

Thanks, that looks like it could be what I need.... One question.... not all temp_link_number's have 3, some have 1, or 2, 3 or even 4.... if it finds a record that doesn't have more then 10 characters, will it just ignore them, or create lines with blank link numbers?

Also, should each select have "as link_number" or does only the first need it as you have shown?

regarding whether there are 3 numbers, or 4, or only 2... use as many subselects in the UNION query as you think there might be positions, and in each one, use a WHERE condition on the length of the column value to determine whether to extract the number

so, extract the substring from 1 for 10 if the length of the column value is >= 10

extract the substring from 12 for 10 if the length of the column value is >= 21

and so on

and yes, you need provide the column alias only on the first subselect (that's how UNION queries work)

That seems to make sense. One (hopefully) last thing... How does the column value command work? Whats the proper syntax for mssql?

you mean the length of the column value?

you can use either LEN(temp_link_number) or DATALENGTH(temp_link_number)

look 'em up in da manual to see how they differ

Awesome, Thanks a ton, I beleive I have it formated as needed now. Just needed the point in the right direction