Using SUBSTRING and RTRIM TogetherSubstring a result and renaming itTRIM() Function With Linked ServerConsolidating a row of data, based on previous rowsWhere should I put nvarchar(max) dimensions in my data warehouse?Is the 8KB row limit a “hard limit”?Unmarshalling Hierarchical JSON in SQL Server 2016+Substring without the first n charactersSql Substring and Search StringSUBSTRING and IF statementproblem with substring in mysql

Why did Missandei say this?

Was the Highlands Ranch shooting the 115th mass shooting in the US in 2019

Thawing Glaciers return to hand interaction

Has everyone forgotten about wildfire?

What dice to use in a game that revolves around triangles?

What is a good way to allow only one non null field in an object

Can you turn a recording upside-down?

Locked my sa user out

Is there any evidence to support the claim that the United States was "suckered into WW1" by Zionists, made by Benjamin Freedman in his 1961 speech

How to avoid making self and former employee look bad when reporting on fixing former employee's work?

Compactness in normed vector spaces.

When do you stop "pushing" a book?

Why do the Avengers care about returning these items in Endgame?

Why are thrust reversers not used to slow down to taxi speeds?

What's the "magic similar to the Knock spell" referenced in the Dungeon of the Mad Mage adventure?

What are these round pads on the bottom of a PCB?

What can cause an unfrozen indoor copper drain pipe to crack?

Two (probably) equal real numbers which are not proved to be equal?

Row vectors and column vectors (Mathematica vs Matlab)

Why is it wrong to *implement* myself a known, published, widely believed to be secure crypto algorithm?

if i accidentally leaked my schools ip address and someone d doses my school am i at fault

How to get MAX value using SOQL when there are more than 50,000 rows

Using wilcox.test() and t.test() in R yielding different p-values

How is Arya still alive?



Using SUBSTRING and RTRIM Together


Substring a result and renaming itTRIM() Function With Linked ServerConsolidating a row of data, based on previous rowsWhere should I put nvarchar(max) dimensions in my data warehouse?Is the 8KB row limit a “hard limit”?Unmarshalling Hierarchical JSON in SQL Server 2016+Substring without the first n charactersSql Substring and Search StringSUBSTRING and IF statementproblem with substring in mysql






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








1















I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City



RIGHT(RTRIM(alt.Address),2)





sql-server-2016 substring string-manipulation trim







share|improve this question
























  • Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?

    – Jacob H
    4 hours ago

















1















I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City



RIGHT(RTRIM(alt.Address),2)





sql-server-2016 substring string-manipulation trim







share|improve this question
























  • Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?

    – Jacob H
    4 hours ago













1












1








1








I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City



RIGHT(RTRIM(alt.Address),2)





sql-server-2016 substring string-manipulation trim







share|improve this question
















I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City



RIGHT(RTRIM(alt.Address),2)





sql-server-2016 substring string-manipulation trim




sql-server-2016 substring string-manipulation trim






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 4 hours ago









Andriy M

16.5k63675




16.5k63675










asked 4 hours ago









Mike JonesMike Jones

626




626












  • Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?

    – Jacob H
    4 hours ago

















  • Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?

    – Jacob H
    4 hours ago
















Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?

– Jacob H
4 hours ago





Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?

– Jacob H
4 hours ago










1 Answer
1






active

oldest

votes


















3














Try this



select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))


A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):



select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))


You can play with both options in this live demo at dbfiddle.uk.






share|improve this answer

























  • You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

    – Andriy M
    4 hours ago












  • Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

    – kevinnwhat
    4 hours ago






  • 1





    As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

    – Andriy M
    4 hours ago






  • 1





    This actually still works with spaces in between city and state abbreviation, give it a shot.

    – kevinnwhat
    4 hours ago






  • 1





    I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

    – Andriy M
    4 hours ago











Your Answer








StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237688%2fusing-substring-and-rtrim-together%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









3














Try this



select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))


A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):



select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))


You can play with both options in this live demo at dbfiddle.uk.






share|improve this answer

























  • You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

    – Andriy M
    4 hours ago












  • Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

    – kevinnwhat
    4 hours ago






  • 1





    As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

    – Andriy M
    4 hours ago






  • 1





    This actually still works with spaces in between city and state abbreviation, give it a shot.

    – kevinnwhat
    4 hours ago






  • 1





    I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

    – Andriy M
    4 hours ago















3














Try this



select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))


A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):



select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))


You can play with both options in this live demo at dbfiddle.uk.






share|improve this answer

























  • You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

    – Andriy M
    4 hours ago












  • Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

    – kevinnwhat
    4 hours ago






  • 1





    As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

    – Andriy M
    4 hours ago






  • 1





    This actually still works with spaces in between city and state abbreviation, give it a shot.

    – kevinnwhat
    4 hours ago






  • 1





    I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

    – Andriy M
    4 hours ago













3












3








3







Try this



select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))


A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):



select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))


You can play with both options in this live demo at dbfiddle.uk.






share|improve this answer















Try this



select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))


A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be):



select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))


You can play with both options in this live demo at dbfiddle.uk.







share|improve this answer














share|improve this answer



share|improve this answer








edited 4 hours ago









Andriy M

16.5k63675




16.5k63675










answered 4 hours ago









kevinnwhatkevinnwhat

8716




8716












  • You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

    – Andriy M
    4 hours ago












  • Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

    – kevinnwhat
    4 hours ago






  • 1





    As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

    – Andriy M
    4 hours ago






  • 1





    This actually still works with spaces in between city and state abbreviation, give it a shot.

    – kevinnwhat
    4 hours ago






  • 1





    I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

    – Andriy M
    4 hours ago

















  • You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

    – Andriy M
    4 hours ago












  • Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

    – kevinnwhat
    4 hours ago






  • 1





    As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

    – Andriy M
    4 hours ago






  • 1





    This actually still works with spaces in between city and state abbreviation, give it a shot.

    – kevinnwhat
    4 hours ago






  • 1





    I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

    – Andriy M
    4 hours ago
















You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

– Andriy M
4 hours ago






You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.

– Andriy M
4 hours ago














Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

– kevinnwhat
4 hours ago





Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.

– kevinnwhat
4 hours ago




1




1





As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

– Andriy M
4 hours ago





As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.

– Andriy M
4 hours ago




1




1





This actually still works with spaces in between city and state abbreviation, give it a shot.

– kevinnwhat
4 hours ago





This actually still works with spaces in between city and state abbreviation, give it a shot.

– kevinnwhat
4 hours ago




1




1





I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

– Andriy M
4 hours ago





I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers

– Andriy M
4 hours ago

















draft saved

draft discarded
















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • 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.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237688%2fusing-substring-and-rtrim-together%23new-answer', 'question_page');

);

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







-

Popular posts from this blog

Canceling a color specificationRandomly assigning color to Graphics3D objects?Default color for Filling in Mathematica 9Coloring specific elements of sets with a prime modified order in an array plotHow to pick a color differing significantly from the colors already in a given color list?Detection of the text colorColor numbers based on their valueCan color schemes for use with ColorData include opacity specification?My dynamic color schemes

Image
Would it be easier to colonise a living world or a dead world? Postal services in Italy - Poste Italiane vs. Friendpost vs. GPS What is /dev/null and why can't I use hx on it? Why are engines with carburetors hard to start in cold weather? How to make a gift without seeming creepy? Without exposing his identity, did Roy help his parents with money so that they can afford to stay in their home? Does the Creighton Method of Natural Family Planning have a failure rate of 3.2% or less? How do I break the broom in Untitled Goose Game? Had there been instances of national states banning harmful imports before the mid-19th C Opium Wars? D&D Monsters and Copyright Is sleeping on the groud in cold weather better than on an air mattress? Transiting through Switzerland by coach with lots of cash quadratic equations on 2 by 2 matrices Always Lubricate Skewers? I pay for a service, but I miss the broadcast Iron-age tools, is there a way to extract heavy metals ...
Read more

Invision Community Contents History See also References External links Navigation menuProprietaryinvisioncommunity.comIPS Community ForumsIPS Community Forumsthis blog entry"License Changes, IP.Board 3.4, and the Future""Interview -- Matt Mecham of Ibforums""CEO Invision Power Board, Matt Mecham Is a Liar, Thief!"IPB License Explanation 1.3, 1.3.1, 2.0, and 2.1ArchivedSecurity Fixes, Updates And Enhancements For IPB 1.3.1Archived"New Demo Accounts - Invision Power Services"the original"New Default Skin"the original"Invision Power Board 3.0.0 and Applications Released"the original"Archived copy"the original"Perpetual licenses being done away with""Release Notes - Invision Power Services""Introducing: IPS Community Suite 4!"Invision Community Release Notes

Image
Internet forum softwarePHP software Internet communityPHPMySQLdatabase management systemInternet forumCharles WarnerMatt MechamJarvis Entertainment GroupIkonboardInvisionFreeAjaxIPS Community ForumsIPS Community Forumsthis blog entry Invision Community From Wikipedia, the free encyclopedia Jump to navigation Jump to search Invision Community Developer(s) Invision Power Services Stable release 4.4.6 / August 19, 2019 ; 34 days ago  ( 2019-08-19 ) Platform PHP / MySQL Type Forum software License Proprietary Website invisioncommunity.com Invision Community (previously known as IPS Community Suite ) is primarily an Internet community software produced by Invision Power Services, Inc. It is written in PHP and uses MySQL as a database management system. Invision Power Services sell applications that each can be bought and installed separately in addition to the Suite, the most widely known being the Internet forum software Invision Power Boar...
Read more

199年 目錄 大件事 到箇年出世嗰人 到箇年死嗰人 節慶、風俗習慣 導覽選單

Image
199年 平年 199年 出自維基百科 跳至導覽 跳至搜尋 世紀： 1世紀 2世紀 3世紀 年代： 160年代 170年代 180年代 190年代 200年代 210年代 220年代 年： 194年 195年 196年 197年 198年 199年 200年 201年 202年 203年 204年 干支年： 己卯年 （ 兔年 ） 年號： 199年 係平年。 目錄 1 大件事 2 到箇年出世嗰人 3 到箇年死嗰人 4 節慶、風俗習慣 大件事 199...
Read more