Posted on 2007-07-30
Last Modified: 2008-03-10
I am using MS SQL 2005 and trying to format some postal sectors.

If the length of the postal sector is greater than 4 I want to take out the spaces of the first 4 characters and if the length is less than 4 i want to replace the spaces in the first 2 characters but I keep getting the error message below:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone help me on this?

Question by:james1977
    LVL 75

    Accepted Solution

    Hello james1977,

    can u post the Query,

    CASE WHEN LEN(PostalSector) > 4 THEN LEFT (PostalSector,4)  ELSE LEFT(PostalSector, 2) END

    Aneesh R

    Author Comment

    Oh sorry, didn't realise I hadn't given you the query!

    IF ((SELECT LEN(PostalSector) FROM PostalSectors) > 4)
          UPDATE PostalSectors
          SET PostalSector = REPLACE(LEFT(PostalSector,4),' ','')
    SELECT PostalSector FROM PostalSectors

    I tried what you sugested and I get the error:
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'CASE'.

    Thanks for your input so far.

    LVL 18

    Assisted Solution

    by:Sham Haque
    try this:

    UPDATE PostalSectors
    set postalsector =
    when len(PostalSector)>4
    then replace(substring(postalsector,1,4),' ','')
    else replace(substring(postalsector,1,2),' ','')


    Author Comment

    That seems to have done it, thanks, I will have a closer look tomorrow then award points, but at a quick glance it looks good. I'll have to do some research on the case statement.

    Thanks again
    LVL 18

    Expert Comment

    by:Sham Haque
    hi james - glad you got the solution:

    aneesh's suggestion of CASE was correct.

    a CASE statement is like an IF, but has specific syntax and can be used in 2 ways - simple or searched - the example above is searched.
    You can use it in a SELECT clause, but also as here in an UPDATE (replacing a SELECT or a value), but also in a WHERE clause, or even an ORDER BY to custom-order a result set!


    Author Comment

    Thanks for your help guys, i've split the points then as I doubt I would have got it to work without your help as well gbshahaq.

    I've used what you both said and come up with this solution that formats my postal sectors perfectly.

    UPDATE PostalSectors
    SET PostalSector = REPLACE(PostalSector, ' ', '')

    UPDATE PostalSectors
    SET postalsector =
    WHEN LEN(PostalSector)=5 THEN LEFT(PostalSector,4) + ' ' + RIGHT(PostalSector,1)
    WHEN LEN(PostalSector)=4 THEN LEFT(PostalSector,3) + ' ' + RIGHT(PostalSector,1)
    WHEN LEN(PostalSector)=3 THEN LEFT(PostalSector,2) + ' ' + RIGHT(PostalSector,1)

    Thanks again

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now