Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Print the portion of a string past a special character

Posted on 2012-09-10
8
Medium Priority
?
323 Views
Last Modified: 2012-09-11
Hi,

If I have a value in a column that contains special characters like:

123456-DD-Group Name-Sub.Group

I need it to show Like this:

DD-GroupName

BUT, if the same column contains a value that looks like this:

Group Name Sub Group

It would have to show like:

GroupName

I was trying to work with the following script, but was unsuccessful. Would someone mind taking a look at this script or helping me by providing the right method?

Here is the script:
SELECT case when (SUBSTRING((
DepartmentDescription
), 8, NULLIF(CHARINDEX('-', (
DepartmentDescription
)) - 1, -1))) IS not null then 
SUBSTRING((
DepartmentDescription
), 8, NULLIF(CHARINDEX('-', (
DepartmentDescription
)) - 1, -1)) else  
SUBSTRING((
DepartmentDescription
       ), CHARINDEX('-', (
DepartmentDescription
)) + 1, LEN((
DepartmentDescription
)))end AS [Name]
   from ceridian_hr_data_952012

Open in new window


and I tried working with this script:
declare @str varchar(100) ='DD-2ndValue-LocationDate'       
select left (@Str, ((case when CHARINDEX('-',@Str,4) = 0 then CHARINDEX('.',@Str,4)  else CHARINDEX('-',@Str,4) 
end )) -1 )

Open in new window


but, I receive the following error:
Invalid length parameter passed to the LEFT or SUBSTRING function.
0
Comment
Question by:susnewyork
  • 4
  • 4
8 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38383712
The two examples you provide are totally different.
First you'll have to define what exactly do you want to extract. If you wish to extract after the first "-" then that's what's gonna get done.

Then we get to your second example, what rule is applied there? There's no special character to start the SUBSTRING, where should the substring end?

That has to be established beforehand and then you can start working on your code.
Let us know what exactly do you want, with one example and then we can give a look at the code.
0
 
LVL 1

Author Comment

by:susnewyork
ID: 38384205
Perhaps I should have worded it differently. There are a few scenarios that I need to be sure to acknowledge within the script.

1. If the column contains something like '123456-DD-Group Name-Sub.Group', I need to format it so that the output shows "DD-GroupName"
What needs to be printed is from character 8 until another hyphen or period is found.

2. If the same column contains a value like "Group Name Sub Group", the script would have to format that value to "GroupNameSubGroup" (made a mistake while typing the initial request).

3. The script cannot break if there is a null or if there is a value without a special character such as " ", "-" and/or "." (space, hyphen, period).

The script would need to be able to format the following values:

Initial Value----------------------------------------------Formatted--------------------------------------------------
123456-DD-Group Name-Sub.Group----------DD-GroupName-------------------------------------------
Group Name Sub Group----------------------------GroupNameSubGroup---------------------------------
InitialGroupNameWithoutSpecChars----------InitialGroupNameWithoutSpecChars-----------

Perhaps using replace() to eliminate spaces from the start will help, but I haven't been able to successfully implement it within the query.

I hope this clears things up.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38384316
If the initial part (i.e. 123456-) in your first case is always in that format (6 digits followed by a hyphen) this code should work.

DECLARE @t TABLE(
myCol VARCHAR(100) NOT NULL)

INSERT INTO @t
SELECT '123456-DD-Group Name.Group'
UNION
SELECT 'Group Name Sub Group'
UNION
SELECT 'InitialGroupNameWithoutSpecChars'

SELECT myCol,
CASE WHEN 
SUBSTRING(myCol,7,1)='-' THEN 
SUBSTRING(REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'),1,CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+9,LEN(myCol)),'-','.')))
ELSE REPLACE(myCol,' ', '')
END myNewCol
FROM @t

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:susnewyork
ID: 38384460
Works great! Would you be able to modify this script to include one more scenario?

if "123456-Group Name", then "GroupName"
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38384486
Sure...

DECLARE @t TABLE(
myCol VARCHAR(100) NOT NULL)

INSERT INTO @t
SELECT '123456-DD-Group Name.Group'
UNION
SELECT 'Group Name Sub Group'
UNION
SELECT 'InitialGroupNameWithoutSpecChars'
UNION
SELECT '123456-Group Name'

SELECT myCol,
CASE WHEN SUBSTRING(myCol,7,1)='-' AND CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))>0 THEN 
	SUBSTRING(REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'),1,CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+9,LEN(myCol)),'-','.')))
WHEN SUBSTRING(myCol,7,1)='-' AND CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))=0 THEN
	REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.')
ELSE REPLACE(myCol,' ', '')
END myNewCol
FROM @t

Open in new window

0
 
LVL 1

Author Comment

by:susnewyork
ID: 38384576
Ok, very close to having this be 100%. I sincerely appreciate your help with this.

A few weird results came up.

1. Initial Value: 308244-MM-Wazooiid ----- After Formatting it shows as: MM.

2. Initial Value: 318111 - MM - Clinic -------- After formatting it shows as: 318111-MM-Clinic (instead of MM-Clinic)

3. Initial Value: 319221-MM-Green Inis ---- After Formatting it shows as: MM.

For #1, it probably has to do with the single word for the group name initial value. #2 is probably being messed up by the space between the 123456 and the "-". #3 is a complete mystery to me.

Thanks again for your help.
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38384612
Sorry about that, give this a try and let me know.

DECLARE @t TABLE(
myCol VARCHAR(100) NOT NULL)

INSERT INTO @t
SELECT '123456-DD-Group Name.Group'
UNION
SELECT 'Group Name Sub Group'
UNION
SELECT 'InitialGroupNameWithoutSpecChars'
UNION
SELECT '123456-Group Name'
UNION
SELECT '308244-MM-Wazooiid'
UNION
SELECT '318111 - MM - Clinic'
UNION
SELECT '319221-MM-Green Inis'

SELECT myCol,
CASE WHEN SUBSTRING(REPLACE(myCol,' ',''),7,1)='-' AND CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))>0 THEN 
	SUBSTRING(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),1,CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+CASE WHEN CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+9,LEN(myCol)),'-','.'))>0 THEN CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))+9,LEN(myCol)),'-','.')) ELSE LEN(myCol) END)
WHEN SUBSTRING(REPLACE(myCol,' ',''),7,1)='-' AND CHARINDEX('.',REPLACE(SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol)),'-','.'))=0 THEN
	SUBSTRING(REPLACE(myCol,' ',''),8,LEN(myCol))
ELSE REPLACE(myCol,' ', '')
END myNewCol
FROM @t

Open in new window


If it still bugs, I'll take a look at it tomorrow morning.
0
 
LVL 1

Author Comment

by:susnewyork
ID: 38386887
Absolutely perfect! Thanks for everything!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question