August 28, 2012 at 7:27 am
amarkhowe (8/28/2012)
Thanks for your help I have been able to get Chris's sql to work,Thanks for all your help.
Can you post the query, Andy? There are a couple of simple tricks to use in the CROSS APPLY parts of the query which are quite efficient. I'd like to ensure that you have picked them up correctly.
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
August 28, 2012 at 7:34 am
sorry this is not working still its only applying it to some of the rows how do I create a ddl script to export the data?
August 28, 2012 at 7:52 am
amarkhowe (8/28/2012)
sorry this is not working still its only applying it to some of the rows how do I create a ddl script to export the data?
Run the query I posted above. This will return rows where the formula won't work because there are fewer than two spaces in the name string. That's all we're interested in now. You are likely to find strings with one word, two words, perhaps NULLs also. Try to give examples of each different pattern. Use the code Anthony posted to create a table variable and overwrite his values with yours.
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
August 28, 2012 at 8:11 am
Ok cheers, is this what you mean?
DECLARE dbo.AccountNumber TABLE (AccountNo ,Name ,pos ,pos)
INSERT INTO dbo.AccountNumber VALUES
(60000000,'Null','Null','Null')
('Null',Gillespie,'Null', 'Null'),
(60000061,'Null','Null','Null'),
('Null','Null','Null','Null'),
('Null', Jacqulyn FamilyNess, 9 ,0),
(60000322, Elroy Ravenclaw19, 6 ,0),
SELECT
AccountNumber,
Name,
space1.pos,
space2.pos
--Title= LEFT(Name,space1.pos-1),
--Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),
--Surname= SUBSTRING(Name,space2.pos+1,8000)
FROM dbo.AccountBase
CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)
CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)
WHERE ISNULL(space1.pos,0) = 0 OR ISNULL(space2.pos,0) = 0
August 28, 2012 at 8:20 am
Thanks Andy, that will do fine. Incorporating into Anthony's dataset;
DECLARE @Account TABLE (AccountNo INT, Name VARCHAR(100))
INSERT INTO @Account VALUES
(60000000,'Mrs Rayford Hufflepuff18'),
(60000001,'Miss Forest Hufflepuff18'),
(60000061,'Miss Bex Ignore'),
(60000068,'Mr Jason Twirl'),
(60000088,'Mrs Katuta Sunger'),
(60000111,'Mrs Corene Ravenclaw19'),
(60000112,'Miss Reta Ravenclaw19'),
(60000132,'Miss Aubrey Ravenclaw19'),
(60000137,'Mr Millicent Ravenclaw19'),
(60000207,'Brigadier Hannah Decorationbextest'),
(60000220,'Professor Jerome Smithe'),
-- new data elements which previously caused query to fail
(60000000,Null),
(Null,'Gillespie'),
(60000061,Null),
(Null,Null),
(Null, 'Jacqulyn FamilyNess'),
(60000322, 'Elroy Ravenclaw19')
SELECT
AccountNo,
Name,
Title= LEFT(Name,space1.pos-1),
Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),
Surname= SUBSTRING(Name,space2.pos+1,8000),
FaultyString = CASE WHEN space1.pos IS NULL OR space2.pos IS NULL THEN 1 ELSE 0 END
FROM @Account
CROSS APPLY (SELECT NULLIF(CHARINDEX(' ', Name,1),0)) space1 (pos)
CROSS APPLY (SELECT NULLIF(CHARINDEX(' ', Name,space1.pos+1),0)) space2 (pos)
If you're not immediately sure how this modification works, look up NULLIF in BOL (Books Online, the SQL Server help) - if you're still stuck, just ask 😉
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
August 28, 2012 at 8:25 am
Brilliant thanks
so this takes into in consideration if the name field is missing a title.
August 28, 2012 at 9:01 am
amarkhowe (8/28/2012)
Brilliant thanksso this takes into in consideration if the name field is missing a title.
The expressions for title and forename will both generate a negative number for the length parameter if space1.pos = 0 OR space2.pos = 0
If CHARINDEX() returns 0 (no space found from the starting position onwards through the string), NULLIF converts the 0 to a NULL.
Passing NULL as the length parameter to LEFT or SUBSTRING returns NULL instead of the error you would get with a negative number.
DECLARE @Account TABLE (AccountNo INT, Name VARCHAR(100))
INSERT INTO @Account VALUES
(60000000,'Mrs Rayford Hufflepuff18'),
(60000001,'Miss Forest Hufflepuff18'),
(60000061,'Miss Bex Ignore'),
(60000068,'Mr Jason Twirl'),
(60000088,'Mrs Katuta Sunger'),
(60000111,'Mrs Corene Ravenclaw19'),
(60000112,'Miss Reta Ravenclaw19'),
(60000132,'Miss Aubrey Ravenclaw19'),
(60000137,'Mr Millicent Ravenclaw19'),
(60000207,'Brigadier Hannah Decorationbextest'),
(60000220,'Professor Jerome Smithe'),
-- new data elements which previously caused query to fail
(60000000,Null),
(Null,'Gillespie'),
(60000061,Null),
(Null,Null),
(Null, 'Jacqulyn FamilyNess'),
(60000322, 'Elroy Ravenclaw19')
SELECT
AccountNo,
Name,
--Title= LEFT(Name,space1.pos-1),
TitleLength = space1.pos-1,
--Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),
ForenameLength = space2.pos-space1.pos-1,
Surname= SUBSTRING(Name,space2.pos+1,8000),
space1.pos,
space2.pos,
FaultyString = CASE WHEN space1.pos IS NULL OR space2.pos IS NULL THEN 1 ELSE 0 END
FROM @Account
CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)
CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)
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 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply