Ufdjrw

2018 MacBook Pro won't let me install macOS High Sierra 10.13 from USB installer

Are there any irrational/transcendental numbers for which the distribution of decimal digits is not uniform?

How to resize main filesystem

Understanding piped commands in GNU/Linux

New Order #6: Easter Egg

Is the time—manner—place ordering of adverbials an oversimplification?

Inverse square law not accurate for non-point masses?

Is this Kuo-toa homebrew race balanced?

Why can't fire hurt Daenerys but it did to Jon Snow in season 1?

What is a more techy Technical Writer job title that isn't cutesy or confusing?

What is the proper term for etching or digging of wall to hide conduit of cables

How to make triangles with rounded sides and corners? (squircle with 3 sides)

How to achieve cat-like agility?

Why did Bronn offer to be Tyrion Lannister's champion in trial by combat?

Is it OK to use the testing sample to compare algorithms?

Why does BitLocker not use RSA?

How to ask rejected full-time candidates to apply to teach individual courses?

What did Turing mean when saying that "machines cannot give rise to surprises" is due to a fallacy?

3D Masyu - A Die

Does the transliteration of 'Dravidian' exist in Hindu scripture? Does 'Dravida' refer to a Geographical area or an ethnic group?

How can I list files in reverse time order by a command and pass them as arguments to another command?

French equivalents of おしゃれは足元から (Every good outfit starts with the shoes)

What was the last profitable war?

How do Java 8 default methods hеlp with lambdas?

Split with and get Results from T-SQL

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-1

I have some T-SQL code that pulls information from a SQL Server table. I need to parse a column and display according to following result set. I'm bew to SQL, it would be great if you don't mark as duplicate and show me how to do it.

Can you please help?

SELECT Account, CTSFirm, AccountName, BOCodeGMI 
FROM StagingEDFACRRBO

BOCodeGMI column contains:

e=01:c=KW:m=10000
c=C-:e=01:m=10000
c=S-:e=01:m=10000
c=06:e=01:m=10
c=07:e=01:m=100
c=W-:e=01:M=10000

Logic to split BOCodeGMI and display two separate columns BOCodeGMI_1 & BOCodeGMI_2:

• If string contains e= then display BOCodeGMI_1 as its corresponding value (ex: 01), if string doesn't contain e=, then display BOCodeGMI_1 as NULL




• If string contains c= then display BOCodeGMI_2 as its corresponding value (ex: C-), if string doesn't contain c= then display BOCodeGMI_2 as NULL

Finally this is how it suppose to show -

BOCodeGMI BOCodeGMI_1 BOCodeGMI_2 
-----------------------------------------------------
e=01:c=KW:m=10000 01 KW
c=C-:e=01:m=10000 01 C-
c=S-:e=01:m=10000 01 S-

sql sql-server tsql parsing split

share|improve this question

edited Mar 9 at 7:06

marc_s

586k13011281272

asked Mar 9 at 0:41

ParthaPartha

63

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is the length of string that want to extract equals always 2 ?
  
  – ahmed abdelqader
  Mar 9 at 0:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, we can assume, it would be 2 for now.
  
  – Partha
  Mar 9 at 0:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  check my below answer.
  
  – ahmed abdelqader
  Mar 9 at 1:13
  

  
  
  
  

add a comment | 

-1

I have some T-SQL code that pulls information from a SQL Server table. I need to parse a column and display according to following result set. I'm bew to SQL, it would be great if you don't mark as duplicate and show me how to do it.

Can you please help?

SELECT Account, CTSFirm, AccountName, BOCodeGMI 
FROM StagingEDFACRRBO

BOCodeGMI column contains:

e=01:c=KW:m=10000
c=C-:e=01:m=10000
c=S-:e=01:m=10000
c=06:e=01:m=10
c=07:e=01:m=100
c=W-:e=01:M=10000

Logic to split BOCodeGMI and display two separate columns BOCodeGMI_1 & BOCodeGMI_2:

• If string contains e= then display BOCodeGMI_1 as its corresponding value (ex: 01), if string doesn't contain e=, then display BOCodeGMI_1 as NULL




• If string contains c= then display BOCodeGMI_2 as its corresponding value (ex: C-), if string doesn't contain c= then display BOCodeGMI_2 as NULL

Finally this is how it suppose to show -

BOCodeGMI BOCodeGMI_1 BOCodeGMI_2 
-----------------------------------------------------
e=01:c=KW:m=10000 01 KW
c=C-:e=01:m=10000 01 C-
c=S-:e=01:m=10000 01 S-

sql sql-server tsql parsing split

share|improve this question

edited Mar 9 at 7:06

marc_s

586k13011281272

asked Mar 9 at 0:41

ParthaPartha

63

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is the length of string that want to extract equals always 2 ?
  
  – ahmed abdelqader
  Mar 9 at 0:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, we can assume, it would be 2 for now.
  
  – Partha
  Mar 9 at 0:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  check my below answer.
  
  – ahmed abdelqader
  Mar 9 at 1:13
  

  
  
  
  

add a comment | 

I have some T-SQL code that pulls information from a SQL Server table. I need to parse a column and display according to following result set. I'm bew to SQL, it would be great if you don't mark as duplicate and show me how to do it.

Can you please help?

SELECT Account, CTSFirm, AccountName, BOCodeGMI 
FROM StagingEDFACRRBO

BOCodeGMI column contains:

e=01:c=KW:m=10000
c=C-:e=01:m=10000
c=S-:e=01:m=10000
c=06:e=01:m=10
c=07:e=01:m=100
c=W-:e=01:M=10000

Logic to split BOCodeGMI and display two separate columns BOCodeGMI_1 & BOCodeGMI_2:

• If string contains e= then display BOCodeGMI_1 as its corresponding value (ex: 01), if string doesn't contain e=, then display BOCodeGMI_1 as NULL




• If string contains c= then display BOCodeGMI_2 as its corresponding value (ex: C-), if string doesn't contain c= then display BOCodeGMI_2 as NULL

Finally this is how it suppose to show -

BOCodeGMI BOCodeGMI_1 BOCodeGMI_2 
-----------------------------------------------------
e=01:c=KW:m=10000 01 KW
c=C-:e=01:m=10000 01 C-
c=S-:e=01:m=10000 01 S-

sql sql-server tsql parsing split

share|improve this question

edited Mar 9 at 7:06

marc_s

586k13011281272

asked Mar 9 at 0:41

ParthaPartha

63

SELECT Account, CTSFirm, AccountName, BOCodeGMI 
FROM StagingEDFACRRBO

e=01:c=KW:m=10000
c=C-:e=01:m=10000
c=S-:e=01:m=10000
c=06:e=01:m=10
c=07:e=01:m=100
c=W-:e=01:M=10000

BOCodeGMI BOCodeGMI_1 BOCodeGMI_2 
-----------------------------------------------------
e=01:c=KW:m=10000 01 KW
c=C-:e=01:m=10000 01 C-
c=S-:e=01:m=10000 01 S-

sql sql-server tsql parsing split

sql sql-server tsql parsing split

share|improve this question

edited Mar 9 at 7:06

marc_s

586k13011281272

asked Mar 9 at 0:41

ParthaPartha

63

share|improve this question

edited Mar 9 at 7:06

marc_s

586k13011281272

asked Mar 9 at 0:41

ParthaPartha

63

edited Mar 9 at 7:06

marc_s

586k13011281272

edited Mar 9 at 7:06

marc_s

586k13011281272

asked Mar 9 at 0:41

ParthaPartha

63

asked Mar 9 at 0:41

ParthaPartha

63

1 Answer
1

active

oldest

votes

1

Try the next query via using CASE, CHARINDEX & SUBSTRING

SELECT 
 BOCodeGMI,
 CASE
 WHEN CHARINDEX('e=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('e=', BOCodeGMI) + 2 , 2) 
 END as BOCodeGMI_1,
 CASE
 WHEN CHARINDEX('c=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('c=', BOCodeGMI) + 2, 2)
 END as BOCodeGMI_2
FROM
 tableName

CASE to go through conditions and return a value.

CHARINDEX to search for a substring in a string, and returns the position.

SUBSTRING to extract some characters from a string.

share|improve this answer

edited Mar 9 at 7:06

marc_s

586k13011281272

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Fantastic Job. You rock!
  
  – Partha
  Mar 9 at 1:31
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55072863%2fsplit-with-and-get-results-from-t-sql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Try the next query via using CASE, CHARINDEX & SUBSTRING

SELECT 
 BOCodeGMI,
 CASE
 WHEN CHARINDEX('e=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('e=', BOCodeGMI) + 2 , 2) 
 END as BOCodeGMI_1,
 CASE
 WHEN CHARINDEX('c=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('c=', BOCodeGMI) + 2, 2)
 END as BOCodeGMI_2
FROM
 tableName

CASE to go through conditions and return a value.

CHARINDEX to search for a substring in a string, and returns the position.

SUBSTRING to extract some characters from a string.

share|improve this answer

edited Mar 9 at 7:06

marc_s

586k13011281272

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Fantastic Job. You rock!
  
  – Partha
  Mar 9 at 1:31
  

  
  
  
  

add a comment | 

1

Try the next query via using CASE, CHARINDEX & SUBSTRING

SELECT 
 BOCodeGMI,
 CASE
 WHEN CHARINDEX('e=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('e=', BOCodeGMI) + 2 , 2) 
 END as BOCodeGMI_1,
 CASE
 WHEN CHARINDEX('c=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('c=', BOCodeGMI) + 2, 2)
 END as BOCodeGMI_2
FROM
 tableName

CASE to go through conditions and return a value.

CHARINDEX to search for a substring in a string, and returns the position.

SUBSTRING to extract some characters from a string.

share|improve this answer

edited Mar 9 at 7:06

marc_s

586k13011281272

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Fantastic Job. You rock!
  
  – Partha
  Mar 9 at 1:31
  

  
  
  
  

add a comment | 

Try the next query via using CASE, CHARINDEX & SUBSTRING

SELECT 
 BOCodeGMI,
 CASE
 WHEN CHARINDEX('e=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('e=', BOCodeGMI) + 2 , 2) 
 END as BOCodeGMI_1,
 CASE
 WHEN CHARINDEX('c=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('c=', BOCodeGMI) + 2, 2)
 END as BOCodeGMI_2
FROM
 tableName

CASE to go through conditions and return a value.

CHARINDEX to search for a substring in a string, and returns the position.

SUBSTRING to extract some characters from a string.

share|improve this answer

edited Mar 9 at 7:06

marc_s

586k13011281272

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924

SELECT 
 BOCodeGMI,
 CASE
 WHEN CHARINDEX('e=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('e=', BOCodeGMI) + 2 , 2) 
 END as BOCodeGMI_1,
 CASE
 WHEN CHARINDEX('c=', BOCodeGMI) > 0 
 THEN SUBSTRING (BOCodeGMI, CHARINDEX('c=', BOCodeGMI) + 2, 2)
 END as BOCodeGMI_2
FROM
 tableName

share|improve this answer

edited Mar 9 at 7:06

marc_s

586k13011281272

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924

edited Mar 9 at 7:06

marc_s

586k13011281272

edited Mar 9 at 7:06

marc_s

586k13011281272

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924

answered Mar 9 at 1:13

ahmed abdelqaderahmed abdelqader

2,778924