Home

How to remove extra spaces for SQL data

%3CLINGO-SUB%20id%3D%22lingo-sub-696350%22%20slang%3D%22en-US%22%3EHow%20to%20remove%20extra%20spaces%20for%20SQL%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696350%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20pulling%20data%20from%20my%20inventory%2C%20but%20the%20Item%23%20have%20extra%20spaces%20so%20i%20can't%20use%20vlookup%20in%20Excel.%20Can%20you%20please%20show%20me%20how%20to%20remove%20the%20extra%20trailing%20and%20leading%20space%3F%20such%20as%20where%20to%20enter%20the%20formula%20and%20how%20in%20SQL%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-696350%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-696657%22%20slang%3D%22en-US%22%3ERE%3A%20How%20to%20remove%20extra%20spaces%20for%20SQL%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696657%22%20slang%3D%22en-US%22%3EHave%20you%20tried%20TRIM%20which%20should%20drop%20the%20extra%20spaces%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-696683%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20How%20to%20remove%20extra%20spaces%20for%20SQL%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696683%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361049%22%20target%3D%22_blank%22%3E%40MickeNy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20you%20ensert%20the%20Trim%20function%20in%20SQL%20statement%3F%20Sorry%26nbsp%3B%20I%20am%20very%20new%20to%20SQL%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-696951%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20How%20to%20remove%20extra%20spaces%20for%20SQL%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F358474%22%20target%3D%22_blank%22%3E%40BDmbarc%3C%2FA%3E%26nbsp%3BI%E2%80%99m%20not%20sure%20how%20to%20do%20it%20within%20an%20SQL%20statement%20but%20assuming%20that%20you%20have%20a%20faulty%20value%20in%20your%20sheet%20that%20you%20want%20to%20use%20in%20a%20sql%20query%20you%20would%20first%20apply%20the%20TRIM%20to%20the%20faulty%20cell%20reducing%20the%20leading%20and%20trailing%20spaces%20from%20it%20and%20then%20use%20the%20resulting%20cell%20as%20your%20sql-statement%20source.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esample%3A%20a1%20contains%20%E2%80%9D%20%26nbsp%3B%20Abc%20%26nbsp%3B%20%E2%80%9D%3C%2FP%3E%3CP%3EWriting%20the%20following%20formula%20TRIM(a1)%20in%20a2%20would%20make%20a2%20content%20%E2%80%9DAbc%E2%80%9D%3C%2FP%3E%3CP%3EThen%20you%20would%20use%20a2%20in%20your%20sql%20statement%20to%20find%20what%20%E2%80%9DAbc%E2%80%9D%20stands%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-697255%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20How%20to%20remove%20extra%20spaces%20for%20SQL%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-697255%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361049%22%20target%3D%22_blank%22%3E%40MickeNy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20try%20your%20method.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hi Everyone,

 

I am pulling data from my inventory, but the Item# have extra spaces so i can't use vlookup in Excel. Can you please show me how to remove the extra trailing and leading space? such as where to enter the formula and how in SQL?

 

Thank you in advance!

4 Replies
Have you tried TRIM which should drop the extra spaces?
Highlighted

Hi @MickeNy 

 

How do you ensert the Trim function in SQL statement? Sorry  I am very new to SQL

 

Thank you

@Deleted I’m not sure how to do it within an SQL statement but assuming that you have a faulty value in your sheet that you want to use in a sql query you would first apply the TRIM to the faulty cell reducing the leading and trailing spaces from it and then use the resulting cell as your sql-statement source.

 

sample: a1 contains ”   Abc   ”

Writing the following formula TRIM(a1) in a2 would make a2 content ”Abc”

Then you would use a2 in your sql statement to find what ”Abc” stands for.

 

I hope this helps!

Thank you @MickeNy 

 

I will try your method. 

 

Thank you so much for your time

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies