August 14, 2015 at 12:02 pm
I have a table where we have created a column that has which contains a column which is a primary key tying back to another table. The problem is this key value could be a different length values so the records look like this.
A00012345
A00001234
A00000123
What I need to do is get the 12345, 1234, or 123 so I can tie back to the other record. Is there a way to use substring
to consistently get back my number?
August 14, 2015 at 12:15 pm
owens.rusty (8/14/2015)
I have a table where we have created a column that has which contains a column which is a primary key tying back to another table. The problem is this key value could be a different length values so the records look like this.A00012345
A00001234
A00000123
What I need to do is get the 12345, 1234, or 123 so I can tie back to the other record. Is there a way to use substring
to consistently get back my number?
Given your data, this solution will work
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @JOIN_TABLE TABLE(JT_ID INT NOT NULL, JT_TEXT VARCHAR(10) NOT NULL);
INSERT INTO @JOIN_TABLE(JT_ID,JT_TEXT) VALUES
(12345 ,'A00012345')
,(1234 ,'A00001234')
,(123 ,'A00000123')
DECLARE @TEST_PK TABLE (PK_VALUE VARCHAR(10));
INSERT INTO @TEST_PK(PK_VALUE) VALUES
('A00012345')
,('A00001234')
,('A00000123')
;
;WITH BASE_DATA AS
(
SELECT
TP.PK_VALUE
,CONVERT(INT,SUBSTRING(TP.PK_VALUE,2,8000),0) AS JOIN_KEY
FROM @TEST_PK TP
)
SELECT
BD.JOIN_KEY
,BD.PK_VALUE
,JT.JT_ID
,JT.JT_TEXT
FROM BASE_DATA BD
INNER JOIN @JOIN_TABLE JT
ON BD.JOIN_KEY = JT.JT_ID;
Results
JOIN_KEY PK_VALUE JT_ID JT_TEXT
----------- ---------- ----------- ----------
12345 A00012345 12345 A00012345
1234 A00001234 1234 A00001234
123 A00000123 123 A00000123
August 14, 2015 at 12:19 pm
owens.rusty (8/14/2015)
I have a table where we have created a column that has which contains a column which is a primary key tying back to another table. The problem is this key value could be a different length values so the records look like this.A00012345
A00001234
A00000123
What I need to do is get the 12345, 1234, or 123 so I can tie back to the other record. Is there a way to use substring
to consistently get back my number?
If you have any influence on projects in the future you should not store two pieces of information in a single table intersection like this. It violates 1NF and you now see first hand what a real pain it is to deal with.
_______________________________________________________________
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/
August 14, 2015 at 12:28 pm
Or you can go the other way around. :hehe:
SELECT *
FROM @JOIN_TABLE JT
INNER JOIN @TEST_PK PK
ON 'A' + RIGHT( 100000000 + JT.JT_ID, 8) = PK.PK_VALUE;
Either way, the design is flawed and hopefully you'll be able to change it.
August 14, 2015 at 12:32 pm
Agreed, long story short. I was involved but this was never needed to tie back to the original table. It was the end result and used like this for an extract into another system. But we are trying to make some reporting work and the best way to do that is use this is in a sense the gold standard. So now having to tie back is a pain.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply