Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

dont insert unknown value into sql server table

Posted on 2016-09-09
16
Medium Priority
?
92 Views
Last Modified: 2016-09-11
I have a query that populates a word table but its allowing what looks like nulls or a word with unknown number of spaces and i am unsure how to deal with that.

use Dictionary

insert into TblCurrentWords (Word)
select Word
from TblWords
where word is not null and word <>'  '
group by word

Open in new window

eeexample.JPG
0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41790851
Use RTRIM and LTRIM to get rid of the spaces:
use Dictionary

insert into TblCurrentWords (Word)
select Word
from TblWords
where word is not null and RTRIM(LTRIM(word))<>''
group by word

Open in new window

1
 
LVL 13

Expert Comment

by:Máté Farkas
ID: 41790854
insert into TblCurrentWords (Word)
select distinct Word
from TblWords
where word > ''

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41790862
i got two responses at the same time and both work, how do i assign equal points? I know I had an issue last time i tried that where one got 1000 and the other expert nothing
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:PeterBaileyUk
ID: 41790866
interestingly the latter didnt work it looked ok in a select but not when i inserted. i will try the first solution now
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41790869
Mark the comments that you liked most (the one that you'll really use) as "Best Solution" and the other one as "Assisted Solution" and assign 250 points to each comment.
0
 

Author Comment

by:PeterBaileyUk
ID: 41790870
one thing i left off as it didnt seem relevant was an additional and.

in any event both solutions permit an entry that looks null or has spaces.

use Dictionary

insert into TblCurrentWords (Word)
select Word
from TblWords
where word is not null and RTRIM(LTRIM(word))<>'' and PATINDEX('%[0-9]%',Word)=0
group by word

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41790871
I dont know what that empty first entry contains
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41790872
Even in my opinion I would always avoid DISTINCT since it's a performance killer and the where word > '' doesn't really filter out the spaces.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41790876
in any event both solutions permit an entry that looks null or has spaces.
Did you clean the table before running our suggestions? If so, please post a screenshot of the results.
0
 

Author Comment

by:PeterBaileyUk
ID: 41790882
yes i deleted the table entries then did a new insert, i will grab a shot again
eedel.JPG
eesel.JPG
0
 

Author Comment

by:PeterBaileyUk
ID: 41790884
i have to leave the empty words in the word table as they are linked to clientcodes and i need to identify them but in this example i am taking a snapshot of the total words in the table to store that.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41790895
The screenshots that I requested is the one after the INSERT command to check the cases that you talked about.
0
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 1000 total points
ID: 41790903
It like a word with a single or no space. In SQL Server, a NULL is different from a blank string (''). Based on the screenshot, it looks like you have a blank string in your source system (for record Id = 1).

Rather than checking for NULLs and strings with spaces separately, you can use the following if you want to skip loading of all NULL/blank values. The LTRIM/RTRIM combination will trim out any spaces from the left and right of the string before performing the comparison.

select Word
from TblWords
where LTRIM(RTRIM(ISNULL(word,''))) <> ''
group by word

Open in new window

0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41790906
I lost internet connectivity in between, so by the time my comment was posted multiple ideas were already exchanged. Sorry for the confusion.
0
 

Author Comment

by:PeterBaileyUk
ID: 41790914
ok Nakuls solution worked so i will share the points between the best two due to the speed and efficiency, is everyone ok with that? that means nakul and vitor.
0
 

Author Comment

by:PeterBaileyUk
ID: 41792301
I know how to select the best and assisted solution but i am damned if i can see how to share the points. It causes great confusion.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 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