January 26, 2017 at 5:37 am
Hi,
I am very new when it comes to T-SQL. I need some assistance. Another person wrote a query to extract data from a column (called snote) in one of the tables. The column is a varchar type column and therefor the data in this column is all text. Here is what is stored in the column:
LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00
The idea is to extract the values between "changed from" and "to" and again after "to"
These are the queries that were written to extract the values:case a.lAction when 2 then ltrim(rtrim((LEFT (right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13), CHARINDEX(' to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13)))))) else '' end AS OLD_PRICE,
case a.lAction when 2 then ltrim(rtrim((right(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13),LEN(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-2)))) else '' end AS NEW_PRICE
This extracts the values fine, but any value that is more than 1000 is stored with a space as the 1000 seperator which then basically makes this value text (according to Excel). I have tried using convert and cast to try and convert this value to decimal or int, but I keep getting "Error converting dat type varchar to xxxxxxx"
Any ideas?
January 26, 2017 at 5:44 am
eddiewillcox - Thursday, January 26, 2017 5:37 AMHi,I am very new when it comes to T-SQL. I need some assistance. Another person wrote a query to extract data from a column (called snote) in one of the tables. The column is a varchar type column and therefor the data in this column is all text. Here is what is stored in the column:
LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00
The idea is to extract the values between "changed from" and "to" and again after "to"
These are the queries that were written to extract the values:
case a.lAction when 2 then ltrim(rtrim((LEFT (right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13), CHARINDEX(' to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13)))))) else '' end AS OLD_PRICE,
case a.lAction when 2 then ltrim(rtrim((right(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13),LEN(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-2)))) else '' end AS NEW_PRICEThis extracts the values fine, but any value that is more than 1000 is stored with a space as the 1000 seperator which then basically makes this value text (according to Excel). I have tried using convert and cast to try and convert this value to decimal or int, but I keep getting "Error converting dat type varchar to xxxxxxx"
Any ideas?
Remove spaces before attempting the convert.
Cast(Replace(YourText,' ','') as int)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 5:47 am
John
January 26, 2017 at 5:54 am
Phil Parkin - Thursday, January 26, 2017 5:44 AMeddiewillcox - Thursday, January 26, 2017 5:37 AMHi,I am very new when it comes to T-SQL. I need some assistance. Another person wrote a query to extract data from a column (called snote) in one of the tables. The column is a varchar type column and therefor the data in this column is all text. Here is what is stored in the column:
LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00
The idea is to extract the values between "changed from" and "to" and again after "to"
These are the queries that were written to extract the values:
case a.lAction when 2 then ltrim(rtrim((LEFT (right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13), CHARINDEX(' to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13)))))) else '' end AS OLD_PRICE,
case a.lAction when 2 then ltrim(rtrim((right(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13),LEN(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-2)))) else '' end AS NEW_PRICEThis extracts the values fine, but any value that is more than 1000 is stored with a space as the 1000 seperator which then basically makes this value text (according to Excel). I have tried using convert and cast to try and convert this value to decimal or int, but I keep getting "Error converting dat type varchar to xxxxxxx"
Any ideas?
Remove spaces before attempting the convert.
Cast(Replace(YourText,' ','') as int)
Thanks. This unfortunately did not work. I get "Conversion failed when converting the varchar value '150.00' to data type int.". And yes there are other values in the column that is lower than 1000
January 26, 2017 at 5:55 am
John Mitchell-245523 - Thursday, January 26, 2017 5:47 AMIf you're doing this to display in Excel then I wouldn't bother putting the thousand separators in at all. Just spit out the numbers, and let Excel handle the format.John
Excel does not like the spaces at all. Trying to use Substitute to replace the spaces does nothing for me. Neither does value formula
January 26, 2017 at 6:45 am
eddiewillcox - Thursday, January 26, 2017 5:54 AMPhil Parkin - Thursday, January 26, 2017 5:44 AMeddiewillcox - Thursday, January 26, 2017 5:37 AMHi,I am very new when it comes to T-SQL. I need some assistance. Another person wrote a query to extract data from a column (called snote) in one of the tables. The column is a varchar type column and therefor the data in this column is all text. Here is what is stored in the column:
LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00
The idea is to extract the values between "changed from" and "to" and again after "to"
These are the queries that were written to extract the values:
case a.lAction when 2 then ltrim(rtrim((LEFT (right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13), CHARINDEX(' to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13)))))) else '' end AS OLD_PRICE,
case a.lAction when 2 then ltrim(rtrim((right(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13),LEN(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-2)))) else '' end AS NEW_PRICEThis extracts the values fine, but any value that is more than 1000 is stored with a space as the 1000 seperator which then basically makes this value text (according to Excel). I have tried using convert and cast to try and convert this value to decimal or int, but I keep getting "Error converting dat type varchar to xxxxxxx"
Any ideas?
Remove spaces before attempting the convert.
Cast(Replace(YourText,' ','') as int)Thanks. This unfortunately did not work. I get "Conversion failed when converting the varchar value '150.00' to data type int.". And yes there are other values in the column that is lower than 1000
Cast(Replace(YourText,' ','') as decimal(18,6))
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 6:48 am
Phil Parkin - Thursday, January 26, 2017 6:44 AMeddiewillcox - Thursday, January 26, 2017 5:54 AMPhil Parkin - Thursday, January 26, 2017 5:44 AMeddiewillcox - Thursday, January 26, 2017 5:37 AMHi,I am very new when it comes to T-SQL. I need some assistance. Another person wrote a query to extract data from a column (called snote) in one of the tables. The column is a varchar type column and therefor the data in this column is all text. Here is what is stored in the column:
LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00
The idea is to extract the values between "changed from" and "to" and again after "to"
These are the queries that were written to extract the values:
case a.lAction when 2 then ltrim(rtrim((LEFT (right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13), CHARINDEX(' to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13)))))) else '' end AS OLD_PRICE,
case a.lAction when 2 then ltrim(rtrim((right(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13),LEN(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-2)))) else '' end AS NEW_PRICEThis extracts the values fine, but any value that is more than 1000 is stored with a space as the 1000 seperator which then basically makes this value text (according to Excel). I have tried using convert and cast to try and convert this value to decimal or int, but I keep getting "Error converting dat type varchar to xxxxxxx"
Any ideas?
Remove spaces before attempting the convert.
Cast(Replace(YourText,' ','') as int)Thanks. This unfortunately did not work. I get "Conversion failed when converting the varchar value '150.00' to data type int.". And yes there are other values in the column that is lower than 1000
Cast(Replace(YourText,' ','') as decimal(18,6))
Thanks, getting error "Error converting data type varchar to numeric"
January 26, 2017 at 6:50 am
eddiewillcox - Thursday, January 26, 2017 6:48 AMPhil Parkin - Thursday, January 26, 2017 6:44 AMeddiewillcox - Thursday, January 26, 2017 5:54 AMPhil Parkin - Thursday, January 26, 2017 5:44 AMeddiewillcox - Thursday, January 26, 2017 5:37 AMHi,I am very new when it comes to T-SQL. I need some assistance. Another person wrote a query to extract data from a column (called snote) in one of the tables. The column is a varchar type column and therefor the data in this column is all text. Here is what is stored in the column:
LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00
The idea is to extract the values between "changed from" and "to" and again after "to"
These are the queries that were written to extract the values:
case a.lAction when 2 then ltrim(rtrim((LEFT (right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13), CHARINDEX(' to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13)))))) else '' end AS OLD_PRICE,
case a.lAction when 2 then ltrim(rtrim((right(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13),LEN(right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-CHARINDEX('to ',right(snote,LEN(snote)-CHARINDEX('changed from ',sNote)-13))-2)))) else '' end AS NEW_PRICEThis extracts the values fine, but any value that is more than 1000 is stored with a space as the 1000 seperator which then basically makes this value text (according to Excel). I have tried using convert and cast to try and convert this value to decimal or int, but I keep getting "Error converting dat type varchar to xxxxxxx"
Any ideas?
Remove spaces before attempting the convert.
Cast(Replace(YourText,' ','') as int)Thanks. This unfortunately did not work. I get "Conversion failed when converting the varchar value '150.00' to data type int.". And yes there are other values in the column that is lower than 1000
Cast(Replace(YourText,' ','') as decimal(18,6))
Thanks, getting error "Error converting data type varchar to numeric"
Also on a side note, running the query with the replace only, does not replace the space in the number at all. I think this is actually the root of the problem
January 26, 2017 at 7:14 am
DECLARE @sNote varchar(1000)
SET @sNote = 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
cast(replace(cast(replace(ltrim(rtrim((LEFT(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13),CHARINDEX(' to ',right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) AS OLD_PRICE,
cast(replace(cast(replace(ltrim(rtrim((right(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13),LEN(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13))-CHARINDEX('to ',right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) AS NEW_PRICEJohn
January 26, 2017 at 7:22 am
John Mitchell-245523 - Thursday, January 26, 2017 7:14 AMWorks for me. The bits I've added are in bold.DECLARE @sNote varchar(1000)
SET @sNote = 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
cast(replace(cast(replace(ltrim(rtrim((LEFT(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13),CHARINDEX(' to ',right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) AS OLD_PRICE,
cast(replace(cast(replace(ltrim(rtrim((right(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13),LEN(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13))-CHARINDEX('to ',right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) AS NEW_PRICEJohn
Thanks John. I'm still getting "Error converting data type varchar to numeric"
January 26, 2017 at 7:37 am
John
January 26, 2017 at 9:43 am
SELECT
OldPrice = CAST(REPLACE(x3.FromNumber,' ','') AS decimal(8,2)),
NewPrice = CAST(REPLACE(x3.ToNumber,' ','') AS decimal(8,2))
FROM (SELECT Note = @sNote) d
CROSS APPLY (SELECT BitWithNumbersIn = SUBSTRING(d.Note,CHARINDEX('changed from ',d.Note)+13,8000)) x1
CROSS APPLY (SELECT p2 = CHARINDEX(' to ',x1.BitWithNumbersIn)) x2
CROSS APPLY (SELECT FromNumber = LEFT(BitWithNumbersIn,x2.p2-1), ToNumber = SUBSTRING(BitWithNumbersIn,x2.p2+4,8000)) x3
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
January 26, 2017 at 10:54 am
eddiewillcox - Thursday, January 26, 2017 6:50 AMAlso on a side note, running the query with the replace only, does not replace the space in the number at all. I think this is actually the root of the problem
Here is some code to prove that space-replacement works:
DECLARE @x VARCHAR(20) = '21 595.00';
SELECT CAST(REPLACE(@x, ' ', '') AS DECIMAL(18, 6));
That makes me think that it is not a space character, but something else. Have you tried analyzing it in Notepad++, to confirm?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 26, 2017 at 10:18 pm
eddiewillcox - Thursday, January 26, 2017 7:22 AMJohn Mitchell-245523 - Thursday, January 26, 2017 7:14 AMWorks for me. The bits I've added are in bold.DECLARE @sNote varchar(1000)
SET @sNote = 'LI-FU-4DV-DCTN-EACH-0059-Monaco Couch Fully Upholstered Exotic Leather 4 Seater, line 1, changed from 21 595.00 to 22 000.00'
SELECT
cast(replace(cast(replace(ltrim(rtrim((LEFT(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13),CHARINDEX(' to ',right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13)))))),' ','') as decimal(18,6)) ,' ','') as decimal(18,6)) AS OLD_PRICE,
cast(replace(cast(replace(ltrim(rtrim((right(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13),LEN(right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13))-CHARINDEX('to ',right(@sNote,LEN(@sNote)-CHARINDEX('changed from ',@sNote)-13))-2)))),' ','') as decimal(18,6)),' ','') as decimal(18,6)) AS NEW_PRICEJohn
Thanks John. I'm still getting "Error converting data type varchar to numeric"
Run the query from your start post, copy-paste the result into Excel and see where the values in NEW_PRICE and/or OLD_PRICE don't look like numeric.
_____________
Code for TallyGenerator
January 27, 2017 at 2:03 am
Do you still have else '' in your case statements?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply