Split with and get Results from T-SQL Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How do you split a list into evenly sized chunks?Inserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableHow do I split a string on a delimiter in Bash?How do I UPDATE from a SELECT in SQL Server?
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
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)
Data science time! April 2019 and salary with experience
The Ask Question Wizard is Live!How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How do you split a list into evenly sized chunks?Inserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableHow do I split a string on a delimiter in Bash?How do I UPDATE from a SELECT in SQL Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
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 displayBOCodeGMI_1as its corresponding value (ex:01), if string doesn't containe=, then displayBOCodeGMI_1as NULLIf string contains
c=then displayBOCodeGMI_2as its corresponding value (ex:C-), if string doesn't containc=then displayBOCodeGMI_2as 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 edited Mar 9 at 7:06
marc_s
586k13011281272
asked Mar 9 at 0:41
ParthaPartha
63
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 displayBOCodeGMI_1as its corresponding value (ex:01), if string doesn't containe=, then displayBOCodeGMI_1as NULLIf string contains
c=then displayBOCodeGMI_2as its corresponding value (ex:C-), if string doesn't containc=then displayBOCodeGMI_2as 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 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 displayBOCodeGMI_1as its corresponding value (ex:01), if string doesn't containe=, then displayBOCodeGMI_1as NULLIf string contains
c=then displayBOCodeGMI_2as its corresponding value (ex:C-), if string doesn't containc=then displayBOCodeGMI_2as 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 edited Mar 9 at 7:06
marc_s
586k13011281272
asked Mar 9 at 0:41
ParthaPartha
63
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 displayBOCodeGMI_1as its corresponding value (ex:01), if string doesn't containe=, then displayBOCodeGMI_1as NULLIf string contains
c=then displayBOCodeGMI_2as its corresponding value (ex:C-), if string doesn't containc=then displayBOCodeGMI_2as 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 sql
sql-server tsql parsing split 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
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
edited Mar 9 at 7:06
marc_s
586k13011281272
586k13011281272
asked Mar 9 at 0:41
ParthaPartha
63
asked Mar 9 at 0:41
ParthaPartha
63
asked Mar 9 at 0:41
ParthaPartha
63
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 |
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
Is the length of string that want to extract equals always 2 ?
– ahmed abdelqader
Mar 9 at 0:55
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
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
check my below answer.
– ahmed abdelqader
Mar 9 at 1:13
add a comment |
1 Answer
1
active
oldest
votes
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.
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
);
);
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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.
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.
edited Mar 9 at 7:06
marc_s
586k13011281272
answered Mar 9 at 1:13
ahmed abdelqaderahmed abdelqader
2,778924
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.
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
edited Mar 9 at 7:06
marc_s
586k13011281272
586k13011281272
answered Mar 9 at 1:13
ahmed abdelqaderahmed abdelqader
2,778924
answered Mar 9 at 1:13
ahmed abdelqaderahmed abdelqader
2,778924
answered Mar 9 at 1:13
ahmed abdelqaderahmed abdelqader
2,778924
2,778924
Fantastic Job. You rock!
– Partha
Mar 9 at 1:31
add a comment |
Fantastic Job. You rock!
– Partha
Mar 9 at 1:31
Fantastic Job. You rock!
– Partha
Mar 9 at 1:31
Fantastic Job. You rock!
– Partha
Mar 9 at 1:31
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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