string field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

string field

Author

Topic

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-09-18 : 09:53:23

Hi, I have a string field which consists of data such as:

Code~Description Code2~Description2......Code8~Description8 and so on.

I need to extract all the codes (which could be varying lengths - 2,3 or 5 chars) and all the corresponding descriptions (which again can be varying lengths).

Any ideas would be appreciated.

Many thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-18 : 10:22:14

see: www.sqlservercentral.com/articles/Tally+Table/72993/

This function will do what you need.

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-09-18 : 11:12:29

Many thanks, will take a look.

Cheers :)

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-09-25 : 05:54:08

had a look at the link but it's not very clear (at least not to me). Can anyone explain how best to achieve the split I'm trying to do? Preferably with some example code?

Many thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-25 : 07:17:25

Download the zip file at the end. It has the function done and several examples

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-09-25 : 07:57:20

Ah...Many thanks

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-09-25 : 09:06:37

Hi gbritton,
I checked out the function and ran it, but it extracts the info as follows which is not the way I need it:

ItemNumber Item
1 Code
2 Description Code2
3 Description2 Code3
4 Description3 Code4
5 Description4 Code5
6 Description5 Code6
7 Description6 Code7
8 Description7

I need it to display all the codes in one column and all the descriptions in another column, e.g.:

ItemNumber Item1 Item2
1 Code Description
2 Code2 Description2
3 Code3 Description3
4 Code4 Description4
5 Code5 Description5
6 Code6 Description6
7 Code7 Description7

any help will be appreciated.

Many thanks

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-25 : 18:19:33

You're going to need a unique value in the original row, such as an identity value or pk col(s), to put the data back together. But, given that, you can kludge the right combination of data back out of this.

For example:


SELECT
    MAX(CASE WHEN row_num % 2 = 1 THEN entry ELSE '' END) AS Code,
    MAX(CASE WHEN row_num % 2 = 0 THEN entry ELSE '' END) AS Description
FROM (
    SELECT
        dt.unique_row_value,
        CASE WHEN which_element IS NULL OR split.ItemNumber = entry_count + 1 THEN split.Item
             WHEN which_element = 1 THEN LEFT(split.Item, LEN(split.Item) - CHARINDEX(' ', REVERSE(split.Item)))
             ELSE RIGHT(split.Item, CHARINDEX(' ', REVERSE(split.Item)) - 1) END AS entry,
        ROW_NUMBER() OVER(PARTITION BY unique_row_value ORDER BY split.ItemNumber) AS row_num
    FROM ( --your_data_table dt
        SELECT 1 AS unique_row_value, 'Code~Description Code2~Description2 Code8~Description 8' AS string_column UNION ALL
        SELECT 2, 'Code11~Description 11 Code43~Description 43 Code57~Description 57 Code99~Description 99'
    ) AS dt
    CROSS APPLY (
        SELECT LEN(dt.string_column) - LEN(REPLACE(dt.string_column, '~', '')) AS entry_count
    ) AS assign_name_to_calc
    CROSS APPLY dbo.[DelimitedSplit8K]( dt.string_column, '~' ) AS split
    LEFT OUTER JOIN (
        SELECT 1 AS which_element UNION ALL
        SELECT 2
    ) AS split_row ON CHARINDEX(' ', split.Item) > 0 AND split.ItemNumber < entry_count + 1
) AS derived
GROUP BY unique_row_value, (row_num + 1) / 2
ORDER BY unique_row_value, (row_num + 1) / 2

Edit: Just noticed there is an error on the last value if the last description includes a space in it, as demonstrated by #2's data above.
Edit2: Adjusted code to deal with that.

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-07 : 08:28:10

Hi Scott,

Many thanks for the above code.
I tried it by first substituting the example string for my real string, in the line SELECT 1 AS unique_row_value, 'NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses' AS string_column and yes it worked as expected.
However, when I changed the text I added in for the real field, it doesn't provide the same results (even though the output of that field is exactly the same)!

The output I get is :

NCA Non-Current
Assets CA Current
Assets PAY Creditors: amounts falling due within one
year CAPRES Capital and
Reserves INC Income EXP
Expenses

Not sure why it's doing that!! The field is a varchar nut I don't think that should cause a problem!

Any ideas?

Many thanks

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-07 : 08:29:11

The expected results are:

NCA Non-Current Assets
CA Current Assets
PAY Creditors: amounts falling due within one year
CAPRES Capital and Reserves
INC Income
EXP Expenses

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-07 : 12:34:00

That string of values worked fine for me. Please re-copy the query code from my original post, as I had to do a second edit to handle embedded spaces in the data values (which I didn't discover until later).

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-10 : 06:09:35

Hi Scott,

I did the above and still the same. What I've found out is that the original long string field is stored as a 'text' field in the database and I've created the variable as a varchar. It's the only difference I can find.
It seems that SQL is interpreting the text field differently to when the text is hard coded (as in your example).
When I hard code it, it works, but I need it to utilise the field.

Regards

Ananta
Starting Member

2 Posts

Posted - 2014-10-10 : 08:47:48

Pls go through below link, it has different approaches to achieve what you are looking for.

~Ananta

Ananta
Starting Member

2 Posts

Posted - 2014-10-10 : 08:49:18

Sorry forgot to include the link in previous post.

Please go through the below link, it has different approaches what you are looking for.

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

~Ananta

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-10 : 12:28:26

Many thanks Ananta.......I shall take a look.
Cheers

ams006
Yak Posting Veteran

55 Posts

Posted - 2014-10-15 : 06:44:56

Still no joy i'm afraid! I've whittled it down to it being a problem with the fact that the field from the db I'm referring to is of a TEXT data type. When I hardcode the contents of that field within single quotes, all works fine! I've no idea where to look to next.

Does SQL treat single quoted text e.g. 'test text here' differently to a TEXT data type field? if so, how and why and what is the resolution?

Thank you

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-15 : 15:05:44

CAST the text to varchar(max) (or nvarchar(max) if you need that).

You can even use the same column name, so you don't have to modify the existing code as much, just use a different alias. For example:


SELECT ca1.text_column, ...
FROM (
) AS dt --dt=data_table
CROSS APPLY (
    SELECT CAST(dt.text_column AS varchar(max)) AS text_column
) AS ca1
CROSS APPLY (
    SELECT SUBSTRING(ca1.text_column, ...)
) AS ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources