May 14, 2013 at 12:38 pm
In the current data set I'm working with in production, the values are only 4 digits, but the CSV file is dropping the data in my staging table with a leading two zeros. So the actual value is 0100 but I'm getting 000100. So I wrote a query (below: SQL SELECT) that captures the RIGHT 4 on the INCOMING_VALUE column. If you run the code below, you'll see the first problem I have. The third and fourth rows are incorrect since I need the RIGHT 5, so the leading 1 & 2 are dropped. Is there a way to dynamically create a process that says RIGHT 4 unless the RIGHT 5th digit >= 1? Ultimately I need to update RIGHT_GREATER_THAN_FIVE with the correct RIGHT 4 or 5 values in the CONVERTED_VALUE column.
SQL SELECT
SELECT
[rgtf].[INCOMING_VALUE]
, (
SELECT
RIGHT([rgtf].[INCOMING_VALUE] , 4)
) AS CONVERTED_DATA
, [rgtf].[DB_VALUE]
, [rgtf].[CAPTURE_VALUE]
FROM
[dbo].[RIGHT_GREATER_THAN_FIVE] AS rgtf
Create table
CREATE TABLE [dbo].[RIGHT_GREATER_THAN_FIVE](
[INCOMING_VALUE] [varchar](15) NULL,
[CONVERTED_VALUE] [varchar](15) NULL,
[DB_VALUE] [varchar](15) NULL,
[CAPTURE_VALUE] [varchar](32) NULL
)
Insert Scripts
INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE]
(
[INCOMING_VALUE]
, [CONVERTED_VALUE]
, [DB_VALUE]
, [CAPTURE_VALUE]
)
VALUES
(
'001000'
, -- INCOMING_VALUE - varchar(15)
''
, -- CONVERTED_VALUE - varchar(15)
'1000'
, -- DB_VALUE - varchar(15)
'8045265CC6AA4671AF6062B1E3C3E6E9' -- CAPTURE_VALUE - varchar(32)
)
INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE]
(
[INCOMING_VALUE]
, [CONVERTED_VALUE]
, [DB_VALUE]
, [CAPTURE_VALUE]
)
VALUES
(
'000200'
, -- INCOMING_VALUE - varchar(15)
''
, -- CONVERTED_VALUE - varchar(15)
'0200'
, -- DB_VALUE - varchar(15)
'7E10E630820042EBB7896D9A560FD998' -- CAPTURE_VALUE - varchar(32)
)
INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE]
(
[INCOMING_VALUE]
, [CONVERTED_VALUE]
, [DB_VALUE]
, [CAPTURE_VALUE]
)
VALUES
(
'010200'
, -- INCOMING_VALUE - varchar(15)
''
, -- CONVERTED_VALUE - varchar(15)
'10200'
, -- DB_VALUE - varchar(15)
'B7EC34B8F60D4EF690BDEA95B2B36C15' -- CAPTURE_VALUE - varchar(32)
)
INSERT INTO [dbo].[RIGHT_GREATER_THAN_FIVE]
(
[INCOMING_VALUE]
, [CONVERTED_VALUE]
, [DB_VALUE]
, [CAPTURE_VALUE]
)
VALUES
(
'022405'
, -- INCOMING_VALUE - varchar(15)
''
, -- CONVERTED_VALUE - varchar(15)
'22405'
, -- DB_VALUE - varchar(15)
'A774DFA5095F45DE99B2B5EAA68FABAD' -- CAPTURE_VALUE - varchar(32)
)
May 14, 2013 at 1:01 pm
Something like this?
It assumes that all the values for INCOMING_VALUE will be numeric.
select right('00000' + cast(CAST(INCOMING_VALUE as int) as varchar(5)), case when CAST(INCOMING_Value as int) > 9999 then 5 else 4 end)
,*
from RIGHT_GREATER_THAN_FIVE
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 2:00 pm
Perfect Suggestion. It worked like a charm. I dropped RIGHT('00000' + CAST(CAST(INCOMING_VALUE AS INT) AS VARCHAR(5)) ,CASE WHEN CAST(INCOMING_Value AS INT) > 9999 THEN 5 ELSE 4 END) in the UPDATE statement and everything worked!
THANK YOU 😀
May 14, 2013 at 2:02 pm
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply