October 15, 2014 at 4:57 am
Hi all,
I'm extracting part of a string field from a table (the field is one very long string field and is set up as a TEXT data type) using a select statement with substring, RIGHT and CHARINDEX functions to extract the correct part I need. The extract looks like this:
NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses
The capital letters before each ~ are codes and the text after each ~ is it's respective description.
when I use the above extract in another script and hard code the values in, encased within single quotes, all works fine.
e.g.
'NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses'
However, If I use my select statement (which includes all the substring, RIGHT and CHARINDEX functions), my results are different and not what I want.
So in essence i'm getting one set of results for a text field and different for text inside single quotes. Any ideas why SQL would be treating single quoted text differently to a TEXT field from the db? and how to fix this as this is bugging me now!
Many thanks
October 15, 2014 at 6:04 am
Not all string functions work with TEXT datatype (http://msdn.microsoft.com/en-GB/library/ms187993.aspx). Try casting to VARCHAR(MAX) before using string manipulation on the column.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2014 at 7:04 am
Hi,
I've tried casting and converting to varchar(max), but still no joy!
October 15, 2014 at 7:26 am
ams00601 (10/15/2014)
Hi,I've tried casting and converting to varchar(max), but still no joy!
Can you post some of your code? Guessing could take a while...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2014 at 8:06 am
Hi Chris,
my code is as below (this is the one that works - with the info hardcoded): If you look at lines 10 and 11 you'll see the hardcoded string.
After this code, I've pasted in the code that doesn't work (the one where I'm referencing the field from the database (field is called USERTYPES1).
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,
'NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses INc de~test' AS string_column --UNION ALL
--SELECT 2, @WorkPaperFinal
) 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
Code that doesn't work:
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,
(SELECT RIGHT(substring((Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = 'criteria'
and AT.IQREGIONID = 'ID No'),
charindex('Working Paper',
(Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = criteria
and AT.IQREGIONID = 'ID No'))+0 ,8000), LEN(substring((Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = 'criteria'
and AT.IQREGIONID = 'ID No'),
charindex('Working Paper',
(Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = 'criteria'
and AT.IQREGIONID = 'ID No'))+0 ,8000)) - 17)) AS string_column --UNION ALL
--SELECT 2, @WorkPaperFinal
) 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
Many thanks
October 15, 2014 at 8:16 am
Looking at what I've posted, it just looks very messy.
in a bid t simplify it:
this hardcoded string works:
'NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses INc de~test'
This doesn't work:
(SELECT RIGHT(substring((Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = 'criteria'
and AT.IQREGIONID = 'ID No'),
charindex('Working Paper',
(Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = criteria
and AT.IQREGIONID = 'ID No'))+0 ,8000), LEN(substring((Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = 'criteria'
and AT.IQREGIONID = 'ID No'),
charindex('Working Paper',
(Select cast (AT.USERTYPES1 as varchar(max))
From
ADMTABLE AT
Where AT.CLASS1 = 'criteria'
and AT.IQREGIONID = 'ID No'))+0 ,8000)) - 17))
Hope that helps
October 15, 2014 at 8:42 am
Gosh that's horrible! Have a play with this:
SELECT x1.UserTypes, x2.pos
FROM ADMTABLE AT
CROSS APPLY (SELECT UserTypes = CAST(AT.USERTYPES1 AS VARCHAR(MAX))) x1
CROSS APPLY (SELECT pos = CHARINDEX('Working Paper',x1.UserTypes)) x2
WHERE AT.CLASS1 = 'criteria'
AND AT.IQREGIONID = 'ID No'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2014 at 9:02 am
Thanks Chris, I'll take a look at that. much appreciated.
You can see why i'm tearing my hair out now lol!
October 16, 2014 at 3:24 am
Hi Chris,
I tried using the code you gave but still no joy!
Any other ideas?
October 16, 2014 at 4:04 am
ams00601 (10/16/2014)
Hi Chris,I tried using the code you gave but still no joy!
Any other ideas?
Not really. The code you've posted so far indicates that you may be a novice at TSQL and as such, unaware of how best to proceed. It's quite difficult to tell what you're trying to do. An explanation and some sample data would definitely help.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 17, 2014 at 3:10 am
Hi Chris,
ok i'm gonna go back to the beginning.
the contents of the text field in the db (USERTYPES1) is the following and I need to extract everything after 'Working Paper ,:
'USERTEXT1, Source currency , USERTEXT2, Description for accounts , USERTEXT20, linked bank account GLACCOUNT , USERBOOL4, control account , USERTEXT3, Category , USERTEXT4, Working Paper ,NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses INc de~test'
I extracted the part I need by using the 'SELECT Right(substring.....' bit of code which is above in the thread.
I obtained a function which is supposed to split that extracted part so I may gather the capital letters (codes) and the text after each ~ (which are their respective descriptions).
The split function doesn't quite do that so I was advised on using another bit of sql to pull out the codes and descriptions into two separate columns (which is my end goal) which only works when I hardcode the extracted part within single quotes (see below), but I need it to be able to reference the sql which extracts the part needed:
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, 'NCA~Non-Current Assets CA~Current Assets PAY~Creditors: amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses INc de~test' AS string_column --UNION ALL
--SELECT 2, @WorkPaperFinal
) 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
Hope this makes sense
Many thanks
October 17, 2014 at 6:40 am
Thanks! Try this...
-- Sample data
CREATE TABLE #ADMTABLE (CLASS1 VARCHAR(15), IQREGIONID VARCHAR(10), USERTYPES1 TEXT)
INSERT INTO #ADMTABLE (CLASS1, IQREGIONID, USERTYPES1)
SELECT 'criteria', 'ID No', 'USERTEXT1, Source currency , USERTEXT2, Description for accounts , USERTEXT20, linked bank account GLACCOUNT ,
USERBOOL4, control account , USERTEXT3, Category , USERTEXT4, Working Paper ,
NCA~Non-Current Assets CA~Current Assets PAY~Creditors:
amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses INc de~test'
-- This query takes you half way to your expected results - the workings show what's happening at each stage
SELECT
x1.UserTypes,
x2.pos,
x3.WorkingString,
x4.ItemNumber,
x4.Item,
x5.*,
x6.*,
gp = x4.ItemNumber%2
FROM #ADMTABLE at
CROSS APPLY (SELECT UserTypes = CAST(at.USERTYPES1 AS VARCHAR(MAX))) x1
CROSS APPLY (SELECT pos = 3 + LEN('Working Paper ,') + CHARINDEX('Working Paper ,',x1.UserTypes)) x2
CROSS APPLY (SELECT WorkingString = SUBSTRING(x1.UserTypes, x2.pos, 8000)) x3
CROSS APPLY dbo.DelimitedSplit8K(x3.WorkingString,'~') x4
CROSS APPLY (SELECT r = charindex(' ',reverse(x4.Item))-1) x5
CROSS APPLY (SELECT
[Code] = LTRIM(CASE WHEN r > 0 THEN RIGHT(x4.Item,x5.r) ELSE x4.Item END),
[Desc] = RTRIM(CASE WHEN r > 0 THEN LEFT(x4.Item,LEN(x4.Item)-x5.r) ELSE x4.Item END)
) x6
WHERE at.CLASS1 = 'criteria'
AND at.IQREGIONID = 'ID No'
-- The easiest way to progress the query above into a solution is to encapsulate the workings into an inline table-valued function.
-- This batch shows how it might work.
DECLARE @WorkingString VARCHAR(8000)
SET @WorkingString = 'NCA~Non-Current Assets CA~Current Assets PAY~Creditors:
amounts falling due within one year CAPRES~Capital and Reserves INC~Income EXP~Expenses INc de~test'
;WITH SplitData AS (
SELECT
ds.ItemNumber,
[Code] = LTRIM(CASE WHEN r > 0 THEN RIGHT(ds.Item,x.r) ELSE ds.Item END),
[Desc] = RTRIM(CASE WHEN r > 0 THEN LEFT(ds.Item,LEN(ds.Item)-x.r) ELSE ds.Item END)
FROM dbo.DelimitedSplit8K(@WorkingString,'~') ds
CROSS APPLY (SELECT r = charindex(' ',reverse(ds.Item))-1) x
)
SELECT s1.Code, s2.[Desc]
FROM SplitData s1
LEFT JOIN SplitData s2
ON s2.ItemNumber = s1.ItemNumber+1
-- Here's the function definition
CREATE FUNCTION [dbo].[if_StringToAccounts]
(
@WorkingString VARCHAR(8000)
)
RETURNS TABLE
AS
RETURN
(
WITH SplitData AS (
SELECT
ds.ItemNumber,
[Code] = LTRIM(CASE WHEN r > 0 THEN RIGHT(ds.Item,x.r) ELSE ds.Item END),
[Desc] = RTRIM(CASE WHEN r > 0 THEN LEFT(ds.Item,LEN(ds.Item)-x.r) ELSE ds.Item END)
FROM dbo.DelimitedSplit8K(@WorkingString,'~') ds
CROSS APPLY (SELECT r = charindex(' ',reverse(ds.Item))-1) x
)
SELECT s1.Code, s2.[Desc]
FROM SplitData s1
LEFT JOIN SplitData s2
ON s2.ItemNumber = s1.ItemNumber+1
)
-- Here's an example of usage
SELECT
x1.UserTypes,
x2.pos,
x3.WorkingString,
x4.*
FROM #ADMTABLE at
CROSS APPLY (SELECT UserTypes = CAST(at.USERTYPES1 AS VARCHAR(MAX))) x1
CROSS APPLY (SELECT pos = 3 + LEN('Working Paper ,') + CHARINDEX('Working Paper ,', x1.UserTypes)) x2
CROSS APPLY (SELECT WorkingString = SUBSTRING(x1.UserTypes, x2.pos, 8000)) x3
CROSS APPLY [dbo].[if_StringToAccounts] (x3.WorkingString) x4
WHERE at.CLASS1 = 'criteria'
AND at.IQREGIONID = 'ID No'
-- This is only a start. For best results, you might want to incorporate ALL of the
-- working logic into the iTVF and possibly integrate it with DelimitedSplit8K too.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply