October 9, 2016 at 8:24 am
Hello,
First time poster on this forum. I've recently been thrown into the world of SQL server, so my experience is limited to the last month or so. I believe my issue is more formatting than anything else, but I have tried so many variation I feel like I'm at the end of my rope.
Working to manipulate a data string where we are physically generating what we are expecting. My problem is that I need to also modify the initial output of our data to make it look like X-XXX-XX. Currently, with this sample, we are seeing the output as X XXX-XX.
Any help/advice would be greatly appreciated!
Thanks!
THEN
CASE
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'H'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-M' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'M'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-U' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(CSM.ticker ,LEN(VALUE) - 1 ,1) = 'U'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-Z' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'X'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-G' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'Z'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-H' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
END
October 9, 2016 at 8:30 am
eandre360 (10/9/2016)
Hello,First time poster on this forum. I've recently been thrown into the world of SQL server, so my experience is limited to the last month or so. I believe my issue is more formatting than anything else, but I have tried so many variation I feel like I'm at the end of my rope.
Working to manipulate a data string where we are physically generating what we are expecting. My problem is that I need to also modify the initial output of our data to make it look like X-XXX-XX. Currently, with this sample, we are seeing the output as X XXX-XX.
Any help/advice would be greatly appreciated!
Thanks!
THEN
CASE
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'H'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-M' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'M'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-U' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(CSM.ticker ,LEN(VALUE) - 1 ,1) = 'U'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-Z' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'X'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-G' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'Z'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-H' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
END
Hi and welcome to the forum.
Can you please provide the DDL (create table) scrips, sample data as an insert statement and the expected output?
😎
There are many ways of doing this but without knowing what your data looks like it is hard to guess which method is the most applicable one.
October 9, 2016 at 9:28 am
Eirikur Eiriksson (10/9/2016)
Hi and welcome to the forum.
Can you please provide the DDL (create table) scrips, sample data as an insert statement and the expected output?
😎
There are many ways of doing this but without knowing what your data looks like it is hard to guess which method is the most applicable one.
Hi Eirikur,
Thanks for the response. Unfortunately not creating a table directly, rather a view. The data output is currently generating G Z6G-H7 with the existing script, which is close...however need to modify the initial output (G Z6) to include a hyphen where a space is currently being generated (G-Z6G-H7). We are using case statements a crossed the board to make our adjustments.
The objective is to generate the next upcoming value G Z6->G H7 = G-Z6G-H7
Right now, when I am trying to mimic an additional SUBSTRING statement, I am getting argument errors, which makes sense but require the additional text to account for our additions.
Thanks again!
October 9, 2016 at 10:28 am
it would help if you could provide details of all the permutations for your column 'value' and your expected results
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 9, 2016 at 11:32 am
J Livingston SQL (10/9/2016)
it would help if you could provide details of all the permutations for your column 'value' and your expected results
Does this help?
Column variations will always look like:
WHEN G H% THEN G M%
WHEN G M% THEN G U%
WHEN G U% THEN G Z%
WHEN G X% THEN G G%
WHEN G Z% THEN H H%
WHEN VALUE IN
(
SELECT
VALUE
FROM
table..security
WHERE
VALUE LIKE 'G%')
THEN
CASE
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'H'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-M' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'M'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-U' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'U'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-Z' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'X'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-G' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'Z'
THEN (VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-H' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
END
October 9, 2016 at 1:44 pm
Quick example, clean the string and stuff it with hyphens
😎
USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA AS
(
SELECT SD_ID, SD_STR FROM
(
VALUES (1,'1 222 33')
,(2,'2-333-44')
,(2,'3 44455')
,(2,'123456')
) AS X( SD_ID, SD_STR)
)
SELECT
SD.SD_ID
,STUFF(STUFF(REPLACE(REPLACE(REPLACE(SD.SD_STR,',',''),' ',''),'-',''),2,0,'-'),6,0,'-') AS OUT_STRING
FROM SAMPLE_DATA SD;
Output
SD_ID OUT_STRING
----------- -------------
1 1-222-33
2 2-333-44
2 3-444-55
2 1-234-56
October 9, 2016 at 4:56 pm
Eirikur Eiriksson (10/9/2016)
Quick example, clean the string and stuff it with hyphens😎
Thanks Eirikur. Unfortunately, I won't be able to hardcode my. Need to figure out a way to either Substring, Left, or insert into the initial value for each then statement.
October 9, 2016 at 9:08 pm
eandre360 (10/9/2016)
Eirikur Eiriksson (10/9/2016)
Quick example, clean the string and stuff it with hyphens😎
Thanks Eirikur. Unfortunately, I won't be able to hardcode my. Need to figure out a way to either Substring, Left, or insert into the initial value for each then statement.
Eirikur's code isn't hard coding anything... The values in the CTE are just there as demonstration values. The solution itself is just the last 4 lines starting at the word "SELECT".
What he doing... He's removing any existing dashes and spaces and then "STUFFing" new dashes into the requested locations.
October 10, 2016 at 1:06 am
eandre360 (10/9/2016)
Eirikur Eiriksson (10/9/2016)
Quick example, clean the string and stuff it with hyphens😎
Thanks Eirikur. Unfortunately, I won't be able to hardcode my. Need to figure out a way to either Substring, Left, or insert into the initial value for each then statement.
As Jason said, the hard coded values in the query is the sample data set for demonstrating the code's functionality. Here is the same with explanations in the comments.
😎
----------------------------------------------------------------------------------------------------------
-- Use a safe database sandbox to play around in, in this case it is called TEEST.
----------------------------------------------------------------------------------------------------------
USE TEEST;
GO
SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------
-- Common Table Expression CTE containing a sample data set which should match the actual data in
-- structure and characteristics.
----------------------------------------------------------------------------------------------------------
;WITH SAMPLE_DATA AS
(
SELECT SD_ID, SD_STR FROM
(
VALUES (1,'1 222 33')
,(2,'2-333-44')
,(2,'3 44455')
,(2,'123456')
) AS X( SD_ID, SD_STR)
)
----------------------------------------------------------------------------------------------------------
-- The actual solution's DML.
----------------------------------------------------------------------------------------------------------
SELECT
SD.SD_ID
,STUFF(STUFF(REPLACE(REPLACE(REPLACE(SD.SD_STR,',',''),' ',''),'-',''),2,0,'-'),6,0,'-') AS OUT_STRING
FROM SAMPLE_DATA SD;
----------------------------------------------------------------------------------------------------------
October 10, 2016 at 4:21 am
SELECT
LINE,
-- ADD REPLACE TO CHANGE SPACES TO DASHES --
(CASE
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'H'
THEN REPLACE((VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-M' + (SUBSTRING(VALUE ,LEN(VALUE) ,1)),' ','-')
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'M'
THEN REPLACE((VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-U' + (SUBSTRING(VALUE ,LEN(VALUE) ,1)),' ','-')
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'U'
THEN REPLACE((VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-Z' + (SUBSTRING(VALUE ,LEN(VALUE) ,1)),' ','-')
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'X'
THEN REPLACE((VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-G' + (SUBSTRING(VALUE ,LEN(VALUE) ,1)),' ','-')
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'Z'
THEN REPLACE((VALUE + SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3)) + '-H' + (SUBSTRING(VALUE ,LEN(VALUE) ,1)),' ','-')
END) as AFTER
FROM
-- EXAMPLE DATA FROM TABLE.SECURITY --
(SELECT 1 as LINE, 'G H2' as VALUE
UNION ALL
SELECT 2,'G-M3'
UNION ALL
SELECT 3,'G U4'
UNION ALL
SELECT 4,'G-X2'
UNION ALL
SELECT 5,'G Z6'
UNION ALL
SELECT 6,'G-Z6'
) as DATA
-- OUTPUT --
LINEAFTER
1G-H2G-M2
2G-M3G-U3
3G-U4G-Z4
4G-X2G-G2
5G-Z6G-H6
6G-Z6G-H6
October 10, 2016 at 4:57 am
Here's one way of doing it:WITH SecurityExamples (Line, Value) AS (
SELECT 1, 'G H2' UNION ALL
SELECT 2, 'G-M3' UNION ALL
SELECT 3, 'G U4' UNION ALL
SELECT 4, 'G-X2' UNION ALL
SELECT 5, 'G Z6' UNION ALL
SELECT 6, 'G-Z6'
)
, Conversion (ValueBefore, ValueAfter) AS (
SELECT 'H', 'M' UNION ALL
SELECT 'M', 'U' UNION ALL
SELECT 'U', 'Z' UNION ALL
SELECT 'X', 'G' UNION ALL
SELECT 'Z', 'H'
)
SELECT
Line
,REPLACE((s.Value) + STUFF(s.Value,3,1,c.ValueAfter),' ','-')
FROM SecurityExamples s
JOIN Conversion c ON SUBSTRING(s.Value,3,1) = c.ValueBefore
John
October 10, 2016 at 8:37 am
Hmm, thanks for the support. I actually think that I managed to format to accommodate this. However, I'm getting an error for something that shouldn't be getting considered in this expression...literally the very first data value:
Conversion failed when converting the varchar value '00080QAF2' to data type int
Could you explain this?
WHEN VALUE IN
(
SELECT
VALUE
FROM
TABLE..csm_security
WHERE
VALUE LIKE 'G%')
THEN
CASE
WHEN SUBSTRING(CSM.ticker ,LEN(VALUE) - 1 ,1) = 'H'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-M' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'M'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-U' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'U'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-Z' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'X'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-G' + CAST(SUBSTRING(VALUE ,LEN(VALUE) ,1) + 1 AS NVARCHAR)
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'Z'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-H' + CAST(SUBSTRING(VALUE ,LEN(VALUE) ,1) + 1 AS NVARCHAR)
END/code]
October 10, 2016 at 8:42 am
You've lost me, I'm afraid. Please will you post the whole statement and the whole error message?
Thanks
John
October 10, 2016 at 8:50 am
eandre360 (10/10/2016)
Hmm, thanks for the support. I actually think that I managed to format to accommodate this. However, I'm getting an error for something that shouldn't be getting considered in this expression...literally the very first data value:Conversion failed when converting the varchar value '00080QAF2' to data type int
Could you explain this?
WHEN VALUE IN(
SELECT
VALUE
FROM
TABLE..csm_security
WHERE
VALUE LIKE 'G%')
THEN
CASE
WHEN SUBSTRING(CSM.ticker ,LEN(VALUE) - 1 ,1) = 'H'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-M' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'M'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-U' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'U'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-Z' + (SUBSTRING(VALUE ,LEN(VALUE) ,1))
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'X'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-G' + CAST(SUBSTRING(VALUE ,LEN(VALUE) ,1) + 1 AS NVARCHAR)
WHEN SUBSTRING(VALUE ,LEN(VALUE) - 1 ,1) = 'Z'
THEN LEN((VALUE) - 3) +'-'+ SUBSTRING(VALUE ,1 ,LEN(VALUE) - 3) + '-H' + CAST(SUBSTRING(VALUE ,LEN(VALUE) ,1) + 1 AS NVARCHAR)
END/code]
The problem is LEN((VALUE) - 3). I think that you're missing a SUBSTRING and your parentheses are wrong. VALUE is a string, but you're trying to subtract 3 from it. It fails to convert VALUE to a int in order to do that.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 8:58 am
Are you sure this CSM.ticker should be in the first WHEN statement - all the others are VALUE
WHEN SUBSTRING(CSM.ticker ,LEN(VALUE) - 1 ,1) = 'H'
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply