for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of them should start with 0, however during the export/import process this 0 got lost, leaving me with zip-codes that only have 4 instead of 5 digits where the leading 0 went missing. Is there a way an SQL command might add this zero to strings only having 4 digits? Thank you

Varchar type column "CodeName", that contain number, prefix or suffix with number it depends on. I want it to be sorted specific way. Below i have defined table with data, CREATE TABLE TempTable ( CodeName VARCHAR(45) NULL ) INSERT INTO TempTable VALUES ('AK-2A'), (NULL), ('PT-4'), ('5'), ('6A'), ('PT-13'), (' '), ('AK-04'), ('FWS-5'), ('6B'), ('AK-1'), ('FWS-03'), ('13'), ('AK-2B'), ('12'), ('FWS-10'), (NULL), ('FWS-17'), ('PT-3B'), ('FWS-117A'), ('PT-01'), ('PT-3A'), ('PT-02'), (''), ('PT-17'), ('PT-06'), ('PT-03'), ('AK-03'); Above insert query including Null and empty value as well. I want to sort it order like ascending first number after character alphabet wise order ex: AK-1, BC-01, PT-01, PT-2, PT-3A, PT-3B, ZX-01, ZX-05, ZX-Z6 etc. For example, this would be an acceptable sort order like this, **Note:** some times if Null or Empty string inserted without Conversion failed, what is the best way to achieve the sort order i have mentioned on example (image) Thank You.

I have a *varchar(200)* column that contains entries such as, ABC123124_A12312 ABC123_A1212 ABC123124_B12312 AC123124_AD12312 A12312_123 etc.. I want to replace a sequence of numbers with a single * so that I can group the distinct non-numeric patterns in the table. The result for this set would be ABC*_A* ABC*_B* AC*_AD* A*_* I have written the following primitive query below, it works correctly, but takes a long time to run on a huge table. **I need help with rewriting or editing it to improve it's performance. SQL Server 2014**

-- 1. replace all numeric characters with '*'
-- 2. replace multiple consecutive '*' with just a single '*'
SELECT REPLACE
        (REPLACE
             (REPLACE
                  (REPLACE
                       (REPLACE
                            (REPLACE
                                 (REPLACE
                                      (REPLACE
                                           (REPLACE
                                                (REPLACE
                                                     (REPLACE
                                                          (REPLACE
                                                               (REPLACE(SampleID, '0', '*'),
                                                                '1', '*'),
                                                           '2', '*'),
                                                      '3', '*'),
                                                 '4', '*'),
                                            '5', '*'),
                                       '6', '*'),
                                  '7', '*'),
                             '8', '*'),
                        '9', '*')
                  , '*', '~*') -- replace each occurrence of '*' with '~*' (token plus asterisk)
             , '*~', '') -- replace in the result of the previous step each occurrence of '*~' (asterisk plus token) with '' (an empty string)
        , '~*', '*') -- replace in the result of the previous step each occurrence of '~*' (token plus asterisk) with '*' (asterisk)
        AS Pattern
FROM TABLE_X

### Data The column includes letters and numbers [A-Za-z0-9] and may also include the special characters / and _. I want to replace any sequence of numbers with *, but I do not know if the entry has special characters, and if so how many special characters. I also do not know how many sequences of numbers are in the entry. All I know is that an entry must have a minimum of 1 number sequence.

I wish to ... 1. ... remove spaces 2. ... delete apostrophes 3. ... replace 'é' and 'è' with 'e' I use the function regexp_replace(). For the moment, I can delete the spaces but poorly. Indeed, when the attribute contains several spaces only one is deleted. I can't process *1.*, *2.*, and *3.* at the same time. Is this possible? Below is a link to my code: https://dbfiddle.uk/22ODtpNS

I have a varchar column in a table in SQL Server that holds a **Base64-encoded** text string, which I would like to decode into its **plain text** equivalent. Does SQL Server have any **native** functionality to handle this type of thing? Here is a sample base64 string: cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA== which decodes to: role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

Due to privacy concerns, I need to hide personal information, while remaining somewhat understandable. I propose to achieve that by hiding everything except the first two characters. The word length should stay the same:

-none
FirstName SecondName LastName -> Fi****** Se******** La******

My attempt is working, but not 100%:

SELECT regexp_replace('Firstname Secondname Lastname', '\y(\S)(\S)','\1' 
       || repeat('*', length('\2')),'g');

Output:

-none
F**rtname S**condname L**stname

With only first character it is easier, and it works, but I need the second character too:

SELECT regexp_replace('Firtname Secondname Lastname', '\Y\w', '*','g');

Output:

-none
F******* S********* L*******

[Demo](https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/7139)

I am running a psql script from the command line with variables something like: psql ...... -v now_utc=$NOW_UTC Then I want to use this in my script like: $$ DECLARE _now_date timestamp := :now_utc; BEGIN -- do something END $$ But I got an error like: > syntax error at or near ':' The script is fine once I change from :now_utc to now() and it works like a charm. The question is how I can pass a variable from the command line to PL/pgSQL code?

I'm using SQL Server 2014 and I have a table with one column containing a [CSV] string: 110,200,310,130,null The output from the table looks like this: I want to select the second column as *multiple columns*, putting each item of the CSV string in a separate column, like this: So I created a function for splitting a string: create FUNCTION [dbo].[fn_splitstring] ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN while (Charindex(@SplitOn,@List)>0) begin insert into @RtnValue (value) select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) end insert Into @RtnValue (Value) select Value = ltrim(rtrim(@List)) return END I would like to use it similar to this: select Val , (select value from tvf_split_string(cchar1,',')) from table1 But the above code obviously won't work, because the function will return more than one row causing the subquery to return more than one value and breaking the code. I can use something like: select Val , (select value from tvf_split_string(cchar1,',') order by id offset 0 rows fetch next 1 rows only ) as col1, (select value from tvf_split_string(cchar1,',') order by id offset 1 rows fetch next 1 rows only ) as col2, ................ from table1 but I don't think it's a good approach. **What is the correct way to do it?**

I have a scenario to replace entire string from a set of comma delimited strings, if partial word matches with another column. I found very complex to explain in words and hence I have explained through a screenshot having various examples. Please help me with stored procedure or a function or query to get results as in **Required_result** column as in below screen shot. I tried STRING_SPLIT function to split values and join the tables. I am struck with the logic to solve this problem.

* Here's a fiddle to show what I'm after . Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in curly braces, mixed in with any other valid text characters. Example: '{1} / {9} ... {12}' With a single SELECT statement, I want to return a string whereby all the integers (and their wrapping braces) have been replaced with a value derived from my table; specifically, the text value for the row having an ID that matches the number found in the source string.... and any characters outside the curly braces remain untouched. Here is an example that fails to complete the task: select replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,'')) from testing; This would return 1 row per row in the testing table. For the row with id value = 13, the '{13}' portion of the string is successfully replaced, but the '{15}' portion is not (and vice versa on row 15). I imagine creating a function that loops through all testing rows and repeatedly attempts replacements would solve the problem. Be that as it may, a straight-up SQL statement would be preferable to looping. **Example Data** +----+-------------------+ | id | display | +----+-------------------+ | 1 | Apple | | 2 | Banana | | 3 | Celery | | 4 | Dragonfruit | | 5 | Eggplant | | 6 | Fenugreek | | 7 | Gourd | | 8 | Honeydew | | 9 | Iceberg Lettuce | | 10 | Jackfruit | | 11 | Kale | | 12 | Lemon | | 13 | Mandarin | | 14 | Nectarine | | 15 | Olive | +----+-------------------+ **Example use cases** select replace('{1} {3}',null,null) -- Returns 'Apple Celery' select replace('{3},{4},{5}',null,null); -- Returns 'Celery,Dragonfruit,Eggplant' select replace('{1} / {9} ... {12}',null,null); -- Returns 'Apple / Iceberg Lettuce ... Lemon' Clearly, the replace keyword does not do the job. PS. If a solution required the format of the string to change in order to facilitate this, that is an option. For example: '#1 / #9 ... #12' (to correlate with the earlier example) In this format, perhaps we could break the string up into a rowset, based on #, take the left characters until we find a non-numeric, join to the testing table based on the numbers taken, replace the # and numbers with the testing table's display value then stuff all those individually modified tokens back into a single string for xml path? I am using SQL Server 2016 which does not support string_agg. That said, if there is a solution using string_agg, I am still interested in reviewing it.

I have a table with below structure : create table student_info ( item_number number, st_firstname varchar2(50), st_lastname varchar2(50), st_score varchar2(50) ) Here is a sample data of the table : item_number | st_firstname | st_lastname | st_score ---------------------------------------------------------------------------- 1 Ali|Reza|Pantea Hashemi|Nosrati|Yaghobi 10|20|20 2 Maryam|Ahmad Moghise|Majlesi 20|20 I need to have the below output: item_number | st_firstname | st_lastname | st_score ---------------------------------------------------------------------------- 1 Ali Hashemi 10 1 Reza Nosrati 20 1 Pantea Yaghobi 20 2 Maryam Moghise 20 2 Ahmad Majlesi 20 I've found that with below query, I can do what I want with one of the columns (Which is st_firstname): select distinct t.item_number, trim(regexp_substr(t.st_firstname, '[^|]+', 1, level)) str from student_info t connect by instr(st_firstname, '|', 1, level - 1) > 0 order by t.item_number The problem is that I don't know how to add other columns(st_lastname , st_lastname) to above query. I was wondering if you could help me here. Thanks in advance

I have a string column that I need to parse into multiple different columns and rows. Sample string value: If the total charge exceeds {$10,000.00} ,pricing is recalculated to be a {50.00}% discount off charges, not to exceed an average daily charge of {$1,000.00}{2,3} Desired output: Price Price_Type Sequence ===== ========== ======== 10000.00 Dollar 1 50.00 Percent 2 1000.00 Dollar 3 A few things to note: 1. I'm only looking to parse Price values that have either '$' or '%' attached to it. '$' is within the brackets before the Price value, '%' is outside the brackets and after the Price value 2. It's possible for Price values to not be encapsulated in brackets, but most are 3. There are values encapsulated in brackets that I don't want to capture 4. Sequence matters. The price value parsed from the left-most side of the string is 1 and so on 5. There are 100,000+ distinct values for the string with significant variation 6. I'm using SQL Server 2017 I'm trying to avoid tedious 100+ lines of CASE statements. I'm guessing the solution will involve a recursive function, recursive CTE, table-valued function, or some combination of these. My attempts at this have not gotten far. There are essentially two looping mechanisms that need to occur: one to get all rows for a single string and another to go through all the distinct strings.

Good afternoon everyone I found just one post here within the last year about this, but it doesn't help my situation. I have been working with MySQL and trying to improve my knowledge of recursive CTE. The version of MySQL is 8.0.19 on a Windows device. The table that I have is generated with:

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
  id int(5) NOT NULL AUTO_INCREMENT,
  source varchar(20) NOT NULL,
  destination varchar(20) NOT NULL,
  route varchar (200) NOT NULL,
  open BOOLEAN DEFAULT true,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Values are entered using:

INSERT INTO test_table VALUES 
  (1, 'STA_A', 'STA_B', 'Line1', true),
  (2, 'STA_B', 'STA_A', 'Line1', true),
  (3, 'STA_B', 'STA_C', 'Line1', true),
  (4, 'STA_C', 'STA_B', 'Line1', true),
  (5, 'STA_C', 'STA_D', 'Line1', true),
  (6, 'STA_D', 'STA_C', 'Line1', true),
  (7, 'STA_D', 'STA_E', 'Line1', true),
  (8, 'STA_E', 'STA_D', 'Line1', true),
  (9, 'STA_K', 'STA_B', 'Line2', true),
  (10, 'STA_B', 'STA_K', 'Line2', true),
  (11, 'STA_B', 'STA_L', 'Line2', true),
  (12, 'STA_L', 'STA_B', 'Line2', true),
  (13, 'STA_L', 'STA_M', 'Line2', true),
  (14, 'STA_M', 'STA_L', 'Line2', true),
  (15, 'STA_M', 'STA_N', 'Line2', true),
  (16, 'STA_N', 'STA_M', 'Line2', true);

Finally, here is the recursive CTE:

SET profiling = 1;
SET @from = 'STA_A';
SET @to = 'STA_M';
SET @via = 'STA_M';
SET @avoid = 'XXXXX';
WITH RECURSIVE cte AS (
	-- Anchor
	SELECT test_table.destination, CONCAT(test_table.source, ' => ', test_table.route, ' => ', test_table.destination) path, 1 length
	FROM test_table
	WHERE test_table.source = @from
	
	UNION ALL

	-- Recursive member
	SELECT test_table.destination, CONCAT(cte.path, ' => ', test_table.route, ' => ', test_table.destination) path, cte.length + 1 length
	FROM cte
	INNER JOIN test_table
	ON test_table.source = cte.destination
	WHERE NOT FIND_IN_SET(test_table.destination, REPLACE(path, ' => ', ','))
	AND open = TRUE
)
SELECT *
FROM cte
WHERE cte.destination = @to
AND FIND_IN_SET(@via, REPLACE(path, ' => ', ','))
AND NOT FIND_IN_SET(@avoid, REPLACE(path, ' => ', ','))
ORDER BY cte.length
LIMIT 500;
SHOW PROFILES;

This pulls successfully the route from Station A to Station M, listing the stations and line segments between the source and destination:

STA_A => Line1 => STA_B => Line2 => STA_L => Line2 => STA_M

What I need to do is remove one of the duplicate line segments (Line2) and the associated station, leaving:

STA_A => Line1 => STA_B => Line2 => STA_M

Is this possible within the CTE, possibly sequential or recursive string manipulation, or would I need to push all the results into a separate "de-duplicating" process? The real database might produce 100 different routes that would need to be de-duplicated in this way and there could be several duplicated line segments in sequence, such as:

STA_A => Line1 => STA_B => Line1 => STA_C => Line1 => STA_D => Line1 => STA_E => Line2 => STA_F => Line2 => STA_G => Line2 => STA_H => Line2 => STA_I => Line2 => STA_J

would de-duplicate to:

STA_A => Line1 => STA_E => Line2 => STA_J

Thank you for your time (and patience!).

Here's some examples to show what I'm after. The source table (the one to have its string values changed):

| string                |
|-----------------------|
| abc${hello}123        |
| def${yolo}321         |
| lol${hello}wow${yolo} |

And here is the lookup table, the one it will be used to replace the values:

| source   | replacement |
|----------|-------------|
| ${hello} | :)          |
| ${yolo}  | :0          |

And this is the result I want:

|  replaced  |
|------------|
| abc:)123   |
| def:0321   |
| lol:)wow:0 |

I tried something like this, but it's not returning what I expect:

WITH replacements AS(
SELECT *
FROM (
VALUES 
('${hello}', ':)'),
('${yolo}', ':0')
) AS sr(source,replacement)
),
strings AS(
SELECT *
FROM (
    VALUES
    ('abc${hello}123'),
    ('def${yolo}321'),
    ('lol${hello}wow${yolo}')
) s(string)
)
SELECT replaced
FROM strings,
LATERAL (
  SELECT replace(string, source, replacement) replaced 
  FROM replacements
) aux
;

It returns:

| replaced         |
|------------------|
| abc:)123         |
| abc${hello}123   |
| def${yolo}321    |
| def:0321         |
| lol:)wow${yolo}  |
| lol${hello}wow:0 |

I'm on SQL Server 2014 and I need to clean whitespace from start and end of a column's content, where whitespace could be simple spaces, tabs or newlines (both \n and \r\n); e.g. ' this content ' should become 'this content' ' \r\n \t\t\t this \r\n content \t \r\n ' should become 'this \r\n content' and so on. I was able to only achieve the first case with UPDATE table t SET t.column = LTRIM(RTRIM(t.column)) but for the other cases it doesn't work.

I have geometries represented as strings (Oracle 18c): with cte as ( select 'LINESTRING ( 1.0 2.0, 3 4)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0)' as txt from dual union all select 'LINESTRING ( 1 2, 3 4, 5 6)' as txt from dual union all select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)' as txt from dual union all select 'LINESTRING M ( 1 2 3.0, 4 5 6)' as txt from dual union all select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt from dual ) select txt from cte TXT -------------------------------------------------------- LINESTRING ( 1.0 2.0, 3 4) LINESTRING M ( 1 2 3, 4 5 6.0) LINESTRING ( 1 2, 3 4, 5 6) LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0) LINESTRING M ( 1 2 3.0, 4 5 6) MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0)) - Ordinates are separated by spaces (X Y M). - Vertices are separated by commas. - Groups/multi-parts are wrapped in brackets and separated by commas. --------------- Using SQL, is there a way to remove the third ordinate (the "M" ordinate) from each vertex? Result: TXT -------------------------------------------------------- LINESTRING ( 1.0 2.0, 3 4) LINESTRING M ( 1 2, 4 5) LINESTRING ( 1 2, 3 4, 5 6) LINESTRING M ( 1 2, 4 5, 7 8.00) LINESTRING M ( 1 2, 4 5) MULTILINESTRING M (( 1 2, 4 5),( 7 8, 10 11)) It would be ok to round out the .0s. Or not.

I want to know which special characters (line breaks, tabs, etc) are in my column of type nvarchar. I know how I can select which rows contain a specific character . e.g. SELECT * FROM your_table WHERE your_column LIKE '%' + CHAR(10) + '%' However, I want to know which columns contain characters not in [a-zA-Z0-9] and then WHICH characters that are. I also looked if I can just do a select on a column to display RAW text with all characters displayed (similar to Microsoft Word's display all characters for example), but I couldn't find how to do this either.

I have a string that has coordinates. - Individual coordinates are separated by spaces. - Vertices (X Y Z coordinates) are separated by commas. - Vertex groups are wrapped in brackets and separated by commas. > Before: > MULTILINESTRING M (( 0.0 5.0 123, 10.0 10.0 456, 30.0 0.0 789),( 50.0 10.0 -123, 60.0 10.0 -100000.0)) I want to replace the third coordinate in each vertex with a new number: > After: > MULTILINESTRING M (( 0.0 5.0 1, 10.0 10.0 1, 30.0 0.0 1),( 50.0 10.0 1, 60.0 10.0 1)) For simplicity, we can use the number 1 as the replacement number. --------------------- What's a good way to replace those numbers in that string?

I have some data like this: | metaphone | lag | | -------- | -------------- | | FLKSW| [null]| | PPS| FLKSW| | PPS| PPS| | PSP| PPS| And I want to compare the string values in both columns on the folowing condition: they're similar (assign some value, like 1) if they share at least 2 chars. Otherwise, they're not similar. So in the example, PPS and PSP would be similar. How can this substring comparison be achieved? I know one approach would be to extract substrings and manually compare them, but it feels hacky and I don't know the maximum number of chars that can occur.

I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings:

  word   |     mkvword
---------+-----------------
 hello   | {world,friend}
 goodbye | {home,forever}
 hi      | {fellas,ladies}

According to the documentation provided for PostgreSQL, appending a value to an array of values should be a simple matter of using the concatenation operator: "||". However, if, I attempt to add the string there, to the array of strings {fellas,ladies} which correspond to the word hi, nothing appears to happen: SELECT mkvword FROM mkvtable WHERE word = 'hi' || 'there'; PostgresSQL appears to be saying as much: mkvword --------- (0 rows) Since there are no errors thrown, I assume things are syntactically okay. What am I doing wrong? My best guess is that psql is getting upset that I'm trying to access the array by using it's relationship to a value from the adjacent column, but that's just an uneducated guess. Perhaps this sort of query doesn't jell well with the concatenation operator? TL;DR: How do you properly concatenate values to arrays stored in table rows?