June 7, 2021 at 2:09 pm
I have a field that coonsistently has data in this style: (b) Joe Brown -12563, (a) Mary Edwards -15425 I need to pull out only the name. I have the below which will pull out the name but it won't trim the space, hyphen, and number off. Any ideas?
SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])) name June 7, 2021 at 2:53 pm
This works most of the time but if I have data like '(t) Jamie Guy -692' it does not:
LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),LEN([AGENT])-10)June 7, 2021 at 3:15 pm
LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1, PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))
;WITH test_data AS (
SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'), ('(t) Jamie Guy -692'),
('(x) Some other person +122') ) AS names(name)
)
SELECT *,
LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1,
PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))
FROM test_data
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 3:26 pm
I get a "Invalid length parameter passed to the LEFT or SUBSTRING function." error plus what if in the number there were a letter like '-16LK5'?
June 7, 2021 at 3:30 pm
As a thought, are you ALWAYS having that format? What I mean is an open bracket, a single character, a closing bracking a space, the name, a space a - and a number?
The reason I ask is if that is ALWAYS the case, you could use something like:
SELECT LEFT(RIGHT([Agent],LEN([Agent])-4),LEN([Agent])-CHARINDEX('-',[Agent])+4)RIGHT is being used to grab all of the characters except the first 4 which gets MOST of the string. LEFT is then taking that string and grabbing all of the characters until the - character. With my testing this seems to work.
IF my assumptions are incorrect, you are going to need to adjust that "- 10" part because that is only valid if the string is a constant length. To use your query:
SELECT LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),CHARINDEX('-',[AGENT])-CHARINDEX(')',[AGENT])-2)That - 2 at the VERY end of the LEFT is to handle the spaces as there is a space after the ) and a space before the - which we don't need to capture.
One thing that I would do though is to put all "magic numbers" (the +1 and -2 in your query or the +4 and -4 in my query) into variables so you can see what they are being used for and why they are needed. In mine, it would be a "RemoveLeadingChars" (at least that is what I'd call it) and give it a value of 4. In yours, the 2 would be "RemoveSpaces" and the 1 would be "RemoveSpace". Not the best names, but I think it works, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 7, 2021 at 3:35 pm
My code works fine with that format of string:
;WITH test_data AS (
SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'), ('(t) Jamie Guy -692'),
('(x) Some other person +122'), ('(y) some other really long name -16LK5') ) AS names(name)
)
SELECT *,
LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1,
PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))
FROM test_data
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 3:43 pm
LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),CHARINDEX('-',[AGENT])-CHARINDEX(')',[AGENT])-2) -works!
June 7, 2021 at 4:35 pm
It would help if you provided test data and expected results - but this is what I think you are looking for:
Declare @testTable Table (TestData varchar(100));
Insert Into @testTable (TestData)
Values ('(b) Joe Brown -12563')
, ('(a) Mary Edwards -15425')
, ('(t) Jamie Guy -692')
, ('(x)John Q. Public-1023')
, ('(v) Jane Doe- 4444');
Select *
, PersonName = rtrim(ltrim(substring(tt.TestData, p1.pos, p2.pos - p1.pos + 1)))
From @testTable As tt
Cross Apply (Values (charindex(')', tt.TestData, 1) + 1)) As p1(pos)
Cross Apply (Values (charindex('-', tt.TestData, 1) - 1)) As p2(pos);
If you are on 2017 or higher - you can replace the rtrim/ltrim with the new trim function. I added a couple of other examples - where the spaces between the data are not the same to show how this works for those differences.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 7, 2021 at 4:44 pm
I'm still getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.
June 7, 2021 at 4:58 pm
I'm still getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.
This is why having sample data is important. The data you have doesn't contain that pattern - either missing the ')' or the '-'. To fix that - you need to know what is missing and what you want the result to be. Assuming you are missing the trailing portion - that is, missing the -NNNN portion:
Declare @testTable Table (TestData varchar(100));
Insert Into @testTable (TestData)
Values ('(b) Joe Brown -12563')
, ('(a) Mary Edwards -15425')
, ('(t) Jamie Guy -692')
, ('(x)John Q. Public-1023')
, ('(v) Jane Doe- 4444')
, ('(c)Missing Dash');
Select *
, PersonName = rtrim(ltrim(substring(tt.TestData, p1.pos, p2.pos - p1.pos + 1)))
From @testTable As tt
Cross Apply (Values (concat(tt.TestData, '-'))) As v(TestData)
Cross Apply (Values (charindex(')', v.TestData, 1) + 1)) As p1(pos)
Cross Apply (Values (charindex('-', v.TestData, 1) - 1)) As p2(pos);
This also works if you are missing the '(x)' portion - since it returns position 1 if no ')' is found. But - if your data contains parenthesis or dashes in other unexpected places then none of the possible solutions would work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 7, 2021 at 5:14 pm
I'm still getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.
See the first link in my signature line below and provide the readily consumable format for the data, please.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2021 at 5:17 pm
All is working now... Cross apply is a great tool
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply